How to write a DELETE query

My First DELETE Statement

Here are the links to the previous posts in this series:

1. My First SELECT Statement
2. My First INSERT Statement
3. My First UPDATE Statement

This week is a much shorter post, where we will learn how to remove data from a table using a DELETE statement.

We will also refresh our memories about the importance of the WHERE clause.

DELETE with a WHERE clause

As with the INSERT statement, a DELETE statement affects the entire row in a table.

To delete information, we identify which row(s) to get rid of using the DELETE keyword, the table name, and a WHERE condition:

DELETE FROM [dbo].[Stores]
WHERE [StoreID] = 2;
GO

In this example, we use the Primary Key StoreID as our row identifier, because it is guaranteed to be unique.

Pro tip: Write the WHERE clause before you write the rest of DELETE statement. It is incredibly easy to accidentally delete every single row in a table.

If that happens, and it’s a production environment, we will have to open up the Run Book to figure out how to restore from a backup.

Remember, the purpose of a WHERE clause is to restrict the data modification using a filter or limit.

DELETE without a WHERE clause

This is an exercise for the reader, with the complete understanding that unless we have a full backup of our demo database, we should never run a DELETE on a table without a WHERE clause.

To run a DELETE without a WHERE clause, simply delete the WHERE condition from the above statement, keeping in mind that the entire table will be emptied out, with no way to go back.

The Final Results

If you’re following along in SQL Server Management Studio, we can run the DELETE statement like so:

DELETE FROM [dbo].[Stores]
WHERE [StoreID] = 2;
GO

Once we have executed the statement (using F5 on the keyboard, or clicking the Execute button in the toolbar), we see a message in the Messages pane:

(1 row(s) affected)

Using our familiar SELECT, we will see the new result set, containing only one row, because the West Edmonton Mall row has been erased from existence:

Congratulations! We have deleted a row from our table, and a SELECT query proved that it was deleted.

Next time, we will find out why a test environment is so important (which was hinted at by the warnings about our WHERE clause). Stay tuned.

Look me up on Twitter if you want to ask any questions about this series, at @bornsql.