MySQL in a Nutshell, 2nd Edition

Name

DELETE

Synopsis

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table
       [WHERE condition] 
       [ORDER BY column [ASC|DESC][,...]]
       [LIMIT row_count]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table[, table]
       FROM table[,...] 
       [WHERE condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table[, table]
       USING table[,...] 
       [WHERE condition]

Use this statement to delete rows of data from a given table. Three basic syntax structures are allowed. The first one shown here is restricted to a single table, whereas the other two can handle multiple tables. For all three, the LOW_PRIORITY keyword instructs the server to wait until there are no queries on the table named before deleting rows. This keyword works only with storage engines that allow table-level locking (i.e., MyISAM, MEMORY, MERGE). The QUICK keyword can be used with MyISAM tables to make deletions faster by not merging leaves in the index’s tree. The IGNORE keyword instructs MySQL to continue even if it encounters errors. You can retrieve error messages afterward with the SHOW WARNINGS statement.

Use the WHERE clause to specify which rows are to be deleted based on a given condition. You can use the DELETE statement in conjunction with the JOIN clause, which is explained later in this chapter.

Here is a simple example of this statement:

DELETE LOW_PRIORITY FROM workreq
WHERE client_id = '1076'
AND status <> 'DONE';

In this example, the client 1076 has closed its account, and management has decided just to delete all of its incomplete work requests. ...