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 as discussed in Chapter 2, "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 in Chapter 7, "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 in the next two chapters.

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.