Deleting Data

The other essential part of any language intended to access databases is the ability to delete rows. Again, not all tables will need this functionality, but more often than not they will—even if just to remove rows that have been added in error. SQL uses the DELETE keyword for this purpose.

Deleting Rows with DELETE

To use DELETE statements, you use the following syntax:

DELETE FROM Table WHERE RowFilter;

which makes DELETE statements the simplest ones you've seen so far. Here Table and RowFilter mean just what they did in the previous example. An appropriate choice of RowFilter can remove one or more rows from the chosen Table.

As with the INTO keyword from the INSERT statement, the FROM keyword is optional. Again, this chapter continues to use it because it makes SQL statements easier to read.

To remove a single row, you'd normally specify the ID (primary key) of the row as follows:

DELETE FROM Customers WHERE CustomerID = 23;

Caution

If you don't specify a WHERE clause, then all rows will be removed from the chosen table.

Selecting based on an identity column is a little safer than selecting by other columns because the values are guaranteed to be unique. Selecting based on a customer name, for example, may result in more than one row being deleted.

DELETING ROW DATA

Execute the following statement against the InstantUniversity database:

DELETE FROM Professor WHERE ProfessorID > 6;

Executing this statement and then viewing the amended table will produce the following result:

   ProfessorID  Name

   -----------  ----------------
   1            Prof. Dawson
   2            Prof. Williams
   3            Prof. Ashby
   4            Prof. Patel
   5            Prof. Jones
   6            Prof. Hwa

The SQL statement in this example deletes all rows from the Professor table with a ProfessorID value of more than six. This removes all the records added in previous examples and takes the table back to its original state, before you started mucking around with it. We must say, we're a little sad to see them go. Wave goodbye for us.

Using DELETE with Different RDBMSs

In addition to the functionality shown previously, similar vendor-specific options are available to those discussed:

SQL Server: Includes facility for overriding the default optimizer behavior. SQL Server also includes the ability to specify a second FROM clause to apply a delete to a related row in a related table. For example, if you have a Books table and an Authors table, and you want to delete one author and all his books from your database, you could use a statement like this:
```
DELETE FROM Books FROM Authors
WHERE Books.AuthorID = Authors.AuthorID AND Books.AuthorID = 1;
```
Oracle: The RETURNING clause can be used to store deleted data in a variable.
MySQL: Includes the LIMIT keyword for limiting the scope of a deletion (which you'll look at in the next chapter).
DB2: Includes the ONLY keyword to limit the scope of the deletion for typed tables and typed views.

SQL Server, Oracle, and DB2 also have the facility for dealing with cursors, which you'll look at later in the book.

Using the TRUNCATE Statement

If you do want to delete all rows in a table, you might want to use the alternative command TRUNCATE TABLE TableName, which is supported by SQL Server and Oracle. The syntax of the TRUNCATE command is as follows:

TRUNCATE TABLE name;

TRUNCATE is optimized to work faster than DELETE because it doesn't log details of the deletion. This also means that rollback is impossible because the data is permanently deleted.

For example, you could execute the following statement against the InstantUniversity database to permanently delete all of the available rooms:

TRUNCATE TABLE Rooms;

This might be useful if the university moved premises.