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

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:

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:

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!