SQL DELETE

You already know how to retrieve, insert and update data in SQL database table. In this chapter we'll learn how to delete data from a table using the SQL DELETE command.

Using our Users table we will illustrate the SQL DELETE usage. One of the users in the Users table (Stephen Grant) has just left the company, and your boss has asked you to delete his record. How do you do that? Consider the SQL statement below:

DELETE FROM Users WHERE LastName = 'Grant'

The first line in the SQL DELETE statement above specifies the table that we are deleting the record(s) from. The second line (the WHERE clause) specifies which rows exactly do we delete (in our case all rows which has LastName of 'Grant'). As you can see the DELETE SQL queries have very simple syntax and in fact are very close to the natural language. But wait, there is something wrong with the statement above! The problem is that we have more than one user having last name of 'Grant', and all users with this last name will be deleted. Because we don't want to do that, we need to find a table field or combination of fields that uniquely identifies the user Stephen Grant. Looking at the Users table an obvious candidate for such a unique field is the Email column (it's not likely that different users use one and the same email). Our improved SQL query which deletes only the record of Stephen Grant's record will look like this:

DELETE FROM Users WHERE Email = 'sgrant@sgrantemail.com'

What happens if you don't specify a WHERE clause in your DELETE query?

DELETE FROM Users

The answer is that all records in the Users table will be deleted. The SQL TRUNCATE statement below will have the exact same effect as the last DELETE statement:

TRUNCATE TABLE Users

The TRUNCATE statement will delete all rows in the Users table, without deleting the table itself.

Be very careful when using DELETE and TRUNCATE, because you cannot undo these statements, and once row(s) are deleted fro your table they are gone forever if you don't have a backup.