MySQL in a Nutshell

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 for one table only. The other two are for multiple tables. For all three, the LOW_PRIORITY flag instructs the server to wait until there are no queries on the table named before deleting rows. The QUICK flag is used with MyISAM tables to make deletions faster by not merging index leaves. The IGNORE flag instructs MySQL to continue even if it encounters errors. You can retrieve error messages afterward with the SHOW WARNINGS statement. You can 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 their incomplete work requests. If a WHERE clause is not given, all the rows for the table would be deleted permanently. Row deletions are performed one row at ...