O'Reilly logo

MySQL in a Nutshell, 2nd Edition by Russell J.T. Dyer

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required