|Part of the
You Can Learn SQL series.
By Ken Brown
Updated: December 11, 2004
A Powerful MSSQL Command to delete information from a Database table
The SQL DELETE statement is the most dangerous word in your arsenal of
SQL statements. With one word and a table name you can inadvertently delete
every row and column in a MSSQL database table. Use the following information
cautiously, but confidently, because we will show you the correct way to make
the SQL DELETE keyword work for you.
The easiest SQL statement to use is "DELETE tablename." This statement will
remove all data in your database table and you will be left with nothing. It is
rare that you would ever want to delete all the information in your table. If
you do, it would be more efficient and faster to use the TRUNCATE keyword.
How do you use the SQL DELETE keyword? You need to qualify it whenever you use
it. Most times you only want to remove one row at a time. So you use your handy
WHERE clause to qualify what needs to be removed.
All these examples we will emulate using the Pubs database provided with
Microsoft's database products. We start by examining the stores table. When we
do a select it returns 7 records. I added a row with a stor_name of "Kennos".
We will remove Kennos store from the SQL table.
What I always do first is create a select statement that returns the
information I want to delete. So when I execute my SQL SELECT command with the
qualifier I return one row containing a stor_name of "Kennos."
Image 1 shows the SQL SELECT statement showing qualifier WHERE clause to return
information from the database.
Since this is what I want to delete then all I have to do is insert that WHERE
clause into my SQL DELETE statement and I will be ready to go.
Image 2 shows the SQL DELETE statement showing qualifier WHERE clause to remove
information from the database table.
See how easy the SQL DELETE statement can be. All the other examples are just
variations of what I have just shown you and are just ways to bring back
information from the table you want to delete.
You can use DELETE on a subquery. Just as an example to show what this means,
let us go back to our SELECT statement from above.
SELECT * FROM stores WHERE stor_name = 'Kennos'
We know that this will return one line where the store name = 'Kennos'. We can
turn this into a subquery, we know what it will return and then use that
subquery with our DELETE statement. The DELETE statement will look like:
DELETE stores WHERE stor_name IN (SELECT stor_name FROM stores WHERE stor_name
Image 3 shows the DELETE statement with subquery.
You can also use the TOP keyword and remove rows from cursors.
The thing to remember with the SQL DELETE command is the power to remove more
than you want. Once that line is gone, it becomes a real pain to bring it back
and can only be retrieved again if you have a back up of that record. So use
great caution and use my little trick of creating your SQL SELECT statement
first and then just taking the qualifying portion and adding it to your SQL
The syntax is DELETE tablename (Qualifier).