The SQL DELETE Statement

Part of the You Can Learn SQL series.
By Ken Brown
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."

SELECT statement showing qualifier WHERE clause to return information from the database.

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.

DELETE statement showing qualifier WHERE clause to remove information from the database.

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 = 'Kennos')

DELETE statement with subquery.

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 DELETE statement.

The syntax is DELETE tablename (Qualifier).

Home| About Us | NewsLetters | Contact Us |

Copyright © 2004-2014 You Can Learn Series

You Can Learn Series Home Page You Can Learn Series
You Can Learn Series Home Page Home            About the creators of YouCanLearnSeriesAbout Us      Contact YouCanLearnSeriesContact Us      Site map of YouCanLearnSeriesSite Map      Privacy Policy of You Can Learn SeriesPrivacy Policy       Terms of Use of You Can Learn Series web siteTerms of Use       YouCanLearnSeries RSS feed for SQL tips and tutorials     
C# and ASP.Net Programming TipsC# and ASP.Net      SQL Tutorial, tips and tricks for DBA'sSQL       Landscaping Tips for Home GardenersLandscaping       WeightLifting Tips for the Young and OldWeightLifting       Good health TipsGoodHealth       Learn about Great Travel DestinationsTravel     Web Business TipsWeb Business     

You Can Learn C# and ASP.Net SeriesC# and ASP.Net

You Can Learn SQL SeriesSQL
  Data Definition LanguageData Definition Language
  Data Manipulation LanguageData Manipulation Language
  What is a DatabaseWhat is a Database
  What is SQLWhat is SQL
  SELECT StatementSELECT Statement
  INSERT StatementINSERT Statement
  DELETE StatementDELETE Statement
  UPDATE StatementUPDATE Statement

You Can Learn Landscaping and Gardening SeriesLandscape Tips
You Can Learn WeightLifting SeriesWeightLift Tips
You Can Learn Good Health SeriesGood Health Tips
You Can Learn Series NewslettersNewsletters
Learn about Great Travel DestinationsTravel
You Can Learn Series Web Business TipsWeb Business Tips

Bill Welter - The Prepared Mind of a Leader

Americas Public Schools