How to Delete Obsolete SQL Data

By Allen G. Taylor

As time passes, data can get old and lose its usefulness. You may want to remove this outdated data from its SQL table. Unneeded data in a table slows performance, consumes memory, and can confuse users. You may want to transfer older data to an archive table and then take the archive offline. In the unlikely event that you ever need that data again, you can recover it.

In the meantime, it doesn’t slow down your everyday processing. Whether or not you decide that obsolete data is worth archiving, you eventually come to the point where you want to delete that data. SQL provides for the removal of rows from database tables by use of the DELETE statement.

You can delete all the rows in a table by using an unqualified DELETE statement, or you can restrict the deletion to only selected rows by adding a WHERE clause. The syntax is similar to the syntax of a SELECT statement, except that you don’t specify columns. After all, if you want to delete a table row, you probably want to remove all the data in that row’s columns.

For example, suppose that your customer, David Taylor, just moved to Switzerland and isn’t going to buy anything from you anymore. You can remove him from your CUSTOMER table by using the following statement:

DELETE FROM CUSTOMER
 WHERE FirstName = 'David' AND LastName = 'Taylor';

Assuming that you have only one customer named David Taylor, this statement makes the intended deletion. If you have two or more customers who share the name David Taylor, you can add more conditions to the WHERE clause to make sure that you delete only the customer you want to remove. If you don’t add a WHERE clause, all customers named David Taylor will be deleted.