1. Computing
Deleting Data from an SQL Table
Join the Discussion
"Share your questions and tap the knowledge of hundreds of your peers."
Mike Chapple

Related Resources
SQL Fundamentals
Access Fundamentals
Choosing a Database

Oftentimes, it becomes necessary to remove obsolete information from a relational database. Fortunately, Structured Query Language provides a flexible DELETE command that can be used to remove some or all of the information stored within a table.

Let's take a brief look at the command's syntax:

DELETE FROM
{table name | view name}
[WHERE search_conditions]

Notice that the command itself is quite simple. There are only two variables -- the table or view to delete from and the search conditions.

Let's first discuss the target of the deletion. According to the ANSI SQL standard, it is possible to delete from either a table or a view. However, I'd strongly encourage you to avoid using the DELETE command (or any data manipulation command, for that matter) on a view. Some versions of SQL simply don't support this syntax and the results of modifying a view can be somewhat unpredictable.

The search_conditions field offers no surprises to students of SQL -- it uses the same format as the search_conditions utilized with the SELECT statement. You can include any comparison operators to limit the data that is removed from the table. Notice that the search_conditions field is actually an optional arguement (hence the square brackets surrounding it). Omission of this argument will result in the deletion of the entire table.

Now let's turn to some examples. Before we get started, we need to create a table and load it with some sample data. We'll create a students table for our small town high school. Execute the following SQL code against your DBMS of choice:

CREATE TABLE students
(
first_name varchar(50),
last_name varchar(50),
id integer PRIMARY KEY
)

INSERT INTO students VALUES ('John', 'Doe', 284)
INSERT INTO students VALUES ('Mike', 'Ryan', 302)
INSERT INTO students VALUES ('Jane', 'Smith', 245)
INSERT INTO students VALUES ('MaryAnn', 'Pringle', 142)
INSERT INTO students VALUES ('Charlotte', 'Bronte', 199)
INSERT INTO students VALUES ('Bill', 'Arlington', 410)

Bill Arlington had stellar academic achievement and was allowed to graduate early. Therefore, we must remove him from the database. As SQL experts, we know that when we want to select a single record, it's prudent to use the primary key in the search condition to prevent accidental removal of similar records. Here's our syntax:

DELETE FROM students
WHERE id = 410

Here are the contents of the modified table:

first_name

last_name

id

Mike

Ryan

302

MaryAnn

Pringle

142

Charlotte

Bronte

199

Jane

Smith

245

John

Doe

284

Now let's try something a bit more complicated -- deleting all of the students with ID numbers between 240 and 290. Here's the SQL:

DELETE FROM students
WHERE id BETWEEN 240 AND 290

and the newly modified table:

first_name

last_name

id

MaryAnn

Pringle

142

Charlotte

Bronte

199

Mike

Ryan

302

And finally, we're sorry to report that our school closed it's doors due to dwindling enrollment. Out of respect for student privacy, we need to remove all of the data from the table. This SQL command will do the trick:

DELETE FROM students

And that's DELETE in a nutshell. Be sure to check back often for new articles on database topics!

Learning More

If you would like to learn more about the Structured Query Language, read Introduction to SQL or sign up for our free Learning SQL e-mail course.



Discuss in my forum

Related Video
Using Meta Data for Search
Export Data From an Excel Sheet to a Word Document

©2014 About.com. All rights reserved.