Delete Syntax
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name [WHERE where_definition] [ORDER BY …] [LIMIT row_count]
or: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table_name[.] [, table_name[.] …] FROM table-references [WHERE where_definition]
or: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name[.] [, table_name[.] …] USING table-references [WHERE where_definition]
DELETE deletes rows from table_name that satisfy the condition given by where_definition , and returns the number of records deleted.
If you issue a DELETE with no WHERE clause, all rows are deleted. If you do this in AUTOCOMMIT mode, this works as TRUNCATE . In MySQL 3.23, DELETE without a WHERE clause will return zero as the number of affected records.
If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form: mysql> DELETE FROM table_name WHERE 1>0;
Note that this is much slower than DELETE FROM table_name with no WHERE clause, because it deletes rows one at a time.
If you specify the keyword LOW_PRIORITY , execution of the DELETE is delayed until no other clients are reading from the table.
For MyISAM tables, if you specify the word QUICK then the storage engine will not merge index leaves during delete, which may speed up certain kind of deletes.
Option IGNORE causes MySQL to ignore all errors during the process of deleting rows. Errors encountered during the parsing stage are processed in the usual manner. Errors that are ignored due to the use of this option are returned as warnings. This option first appeared in version 4.1.1.
In MyISAM tables, deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE TABLE is easier, but myisamchk is faster.
The first multiple-table delete format is supported starting from MySQL 4.0.0. The second multiple-table delete format is supported starting from MySQL 4.0.2.
The idea is that only matching rows from the tables listed before the FROM or before the USING clause are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching.
The .* after the table names is there just to be compatible with Access : DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id
or: DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id
In the above case we delete matching rows only from tables t1 and t2 .
The examples show inner joins using the comma operator, but multiple-table DELETE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN .
If an ORDER BY clause is used (available from MySQL 4.0.0), the rows will be deleted in that order. This is really useful only in conjunction with LIMIT . For example: DELETE FROM somelog WHERE user = ‘jcole’ ORDER BY timestamp LIMIT 1
This will delete the oldest entry (by timestamp ) where the row matches the WHERE clause.
The MySQL-specific LIMIT row_count option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn’t take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.
From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE