DELETE

Use this MySQL statement to delete rows of data from a given table. Be careful with this one: there's basically no UNDO statement.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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]

Explanation

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.

Examples

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. If a WHERE clause is not given, all of the rows for the table would be deleted permanently.

If you want to delete all of the data in a table, you can use this statement without the WHERE clause, but it's slow because deletions are performed one row at a time. The same result can be obtained faster with the TRUNCATE statement. However, the TRUNCATE statement doesn't return the number of rows deleted, so you can use DELETE if that's important to you.

To delete only a certain number of rows in a table, use the LIMIT clause to specify the number of rows to delete. To delete a specific number of rows for a particular range of column values, use the ORDER BY clause along with the LIMIT clause. For example, suppose an account executive informed the database administrator that the last four work requests she entered for a particular client (1023) need to be deleted. The database administrator could enter the following to delete those rows:

DELETE FROM workreq
WHERE client_id = '1023'
ORDER BY request_date DESC
LIMIT 4;

In this example, the rows are first ordered by the date of the work request, in descending order (latest date first). Additional columns may be given in a comma-separated list for the ordering. The LIMIT clause is used to limit the number of deletions to the first four rows of the results of the WHERE clause and the ORDER BY clause.

The second syntax for this statement allows other tables to be referenced. In the first example shown here, the database administrator wants to delete rows representing a particular client from the work request table, but doesn't know the client account number. However, the database administrator knows the client's name begins with Cole, so the following could be entered to delete the records:

DELETE workreq FROM workreq, clients
WHERE workreq.client_id = clients.client_id
AND client_name LIKE 'Cole%';

In this example, the table in which rows will be deleted is given after the DELETE keyword. It's also given in the list of tables in the FROM clause, which specifies the table from which information will be obtained to determine the rows to delete. The two tables are joined in the WHERE clause on the client identification number column in each. Using the LIKE keyword, the selection of rows is limited to clients with a name beginning with the name Cole. Incidentally, if more than one client has a name beginning with Cole, the rows for both will be deleted from the work request table. You can delete rows in more than one table with a single statement by listing the tables in a comma-separated list after the DELETE keyword. For example, suppose that it has been decided to delete not only the work requests for the client, but also the row for the client in the clients table:

DELETE workreq, clients FROM workreq, clients
WHERE workreq.clientid = clients.clientid
AND client_name LIKE 'Cole%';

Notice that the only syntactical difference between this statement and the one in the previous example is that this statement lists both tables for which rows are to be deleted after the DELETE keyword and before the FROM clause. Deletions are permanent, so care should be taken as to which tables are listed for deletion.

The third syntax operates in the same way as the second one, but offers a couple of keywords that may be preferred for clarity. If the previous example were entered with this third syntax, it would look like this:

DELETE FROM workreq USING workreq, clients
WHERE workreq.clientid = clients.clientid
AND client_name LIKE 'Cole%';

Notice that the table from which rows will be deleted is listed in the FROM clause. The tables that the statement will search to for information to determine which rows to delete are listed in the USING clause. The results of statements using this syntax structure and the previous one are the same. It's just a matter of style preference and compatibility with other database systems.

Although MySQL will eventually reuse space allocated for deleted rows, you can compact a table which has had many rows deleted by using the OPTIMIZE TABLE statement or the myisamchk utility.