Using DELETE

The DELETE SQL statement allows us to delete rows from a table. There are some delete statements in Listing 5.1, for example,

delete from department;

In this form, the delete statement will delete all the rows from the department table.

We can also limit which rows are deleted using a WHERE clause, for example,

delete from department where name='Asset Management';

This will only delete the rows matching the criteria in the where clause. In this case, only rows in which the department name is 'Asset Management' will be deleted.

It is unusual to want to delete all the rows from a table. However, because this is the shortest form of the delete statement, you may sometimes type it by accident without a WHERE clause. You can save yourself this anguish by switching on the -–safe-updates or –-i-am-a-dummy command-line options of the mysql client , "Quick Tour." These options prevent you from deleting (or updating) rows without specifying a key constraint in the WHERE clause. That is, you need to specify that you want to delete only rows containing certain key values.

This is the general form of the DELETE statement from the MySQL manual:

DELETE [LOW_PRIORITY] [QUICK] FROM table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]

or

DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]
FROM table-references
[WHERE where_definition]

or

DELETE [LOW_PRIORITY] [QUICK]
FROM table_name[.*] [, table_name[.*] ...]
USING table-references
[WHERE where_definition]

The first form is the one we have seen examples of so far.

The other two forms are designed to allow us to delete rows from one or more tables with references to other tables. For example:

delete employee, employeeSkills
from employee, employeeSkills, department
where employee.employeeID = employeeSkills.employeeID
and employee.departmentID = department.departmentID
and department.name='Finance';

This example deletes all the employees who work for the Finance department and erases all records of their skills. Note that rows are deleted from employee and employeeSkills (the tables listed in the initial where clause), but not department (because it is listed only in the from clause).

The tables in the initial delete clause will have rows deleted from them, whereas the tables listed in the from clause are used for searching for data and will not have rows deleted unless they are also listed in the delete clause.

Note that this is quite a complex example because it involves three tables! We need three tables to illustrate this example, but we suggest that you come back and review the WHERE clause after reading about joins, "Advanced Queries."

We have used a couple of new things in this where clause: the AND operator and the table.column notation. We have used AND to join our conditions together. This is a simple Boolean AND. We have also used the notation employee.employeeID. This notation means "the employeeID column of the employee table." We will revisit both of these things in more detail..

The third form of DELETE is similar to the second form, except that, in this case, we delete only from the tables listed in the FROM clause while referring to the tables in the USING clause. For example:

delete from employee, employeeSkills
using employee, employeeSkills, department
where employee.employeeID = employeeSkills.employeeID
and employee.departmentID = department.departmentID
and department.name='Finance';

This is equivalent to the preceding example, except that it uses an alternative syntax.

There are a couple of other optional clauses in the general form of the DELETE statement:

The LOW_PRIORITY clause works in the same way as it does in the INSERT statement.

Specifying QUICK may speed up the DELETE statement by telling MySQL not to do some of its housekeeping on indexes while deleting from the table.

The ORDER BY clause specifies the order in which to delete rows. This is most useful in conjunction with the LIMIT clause—we may want to delete the oldest n rows from a table, for example.

The LIMIT clause allows us to set a maximum number of rows that can be deleted by the DELETE statement. This is useful either in conjunction with the ORDER BY clause or to save us from accidentally deleting too many rows.