Skip navigation links
12.2.3 DO Syntax »
Section Navigation      [Toggle]

12.2.2. DELETE Syntax

Single-table syntax: 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax: 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or: 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

For the single-table syntax, the DELETE statement deletes rows from tbl_name and returns a count of the number of deleted rows. This count can be obtained by calling the ROW_COUNT() function (see Section 11.11.3, “Information Functions”). The WHERE clause, if given, specifies the conditions that identify which rows to delete. With no WHERE clause, all rows are deleted. If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted.

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

where_condition is an expression that evaluates to true for each row to be deleted. It is specified as described in Section 12.2.8, “SELECT Syntax”.

Currently, you cannot delete from a table and select from the same table in a subquery.

You need the DELETE privilege on a table to delete rows from it. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause.

As stated, a DELETE statement with no WHERE clause deletes all rows. A faster way to do this, when you do not need to know the number of deleted rows, is to use TRUNCATE TABLE. However, within a transaction or if you have a lock on the table, TRUNCATE TABLE cannot be used whereas DELETE can. See Section 12.2.10, “TRUNCATE Syntax”, and Section 12.4.5, “LOCK TABLES and UNLOCK TABLES Syntax”.

If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is not reused for a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE clause) in autocommit mode, the sequence starts over for all storage engines except InnoDB and MyISAM. There are some exceptions to this behavior for InnoDB tables, as discussed in Section 13.6.4.3, “AUTO_INCREMENT Handling in InnoDB”.

For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. In this case, reuse of values deleted from the top of the sequence occurs even for MyISAM tables. See Section 3.6.9, “Using AUTO_INCREMENT”.

The DELETE statement supports the following modifiers:

  • If you specify LOW_PRIORITY, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE).

  • For MyISAM tables, if you use the QUICK keyword, the storage engine does not merge index leaves during delete, which may speed up some kinds of delete operations.

  • The IGNORE keyword 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 IGNORE are returned as warnings.

The speed of delete operations may also be affected by factors discussed in Section 7.2.23, “Speed of DELETE Statements”.

In MyISAM tables, deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row 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 to use, but myisamchk is faster. See Section 12.5.2.5, “OPTIMIZE TABLE Syntax”, and Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.

The QUICK modifier affects whether index leaves are merged for delete operations. DELETE QUICK is most useful for applications where index values for deleted rows are replaced by similar index values from rows inserted later. In this case, the holes left by deleted values are reused.

DELETE QUICK is not useful when deleted values lead to underfilled index blocks spanning a range of index values for which new inserts occur again. In this case, use of QUICK can lead to wasted space in the index that remains unreclaimed. Here is an example of such a scenario:

  1. Create a table that contains an indexed AUTO_INCREMENT column.

  2. Insert many rows into the table. Each insert results in an index value that is added to the high end of the index.

  3. Delete a block of rows at the low end of the column range using DELETE QUICK.

In this scenario, the index blocks associated with the deleted index values become underfilled but are not merged with other index blocks due to the use of QUICK. They remain underfilled when new inserts occur, because new rows do not have index values in the deleted range. Furthermore, they remain underfilled even if you later use DELETE without QUICK, unless some of the deleted index values happen to lie in index blocks within or adjacent to the underfilled blocks. To reclaim unused index space under these circumstances, use OPTIMIZE TABLE.

If you are going to delete many rows from a table, it might be faster to use DELETE QUICK followed by OPTIMIZE TABLE. This rebuilds the index rather than performing many index block merge operations.

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 given DELETE statement does not take too much time. You can simply repeat the DELETE statement until the number of affected rows is less than the LIMIT value.

If the DELETE statement includes an ORDER BY clause, rows are deleted in the order specified by the clause. This is useful primarily in conjunction with LIMIT. For example, the following statement finds rows matching the WHERE clause, sorts them by timestamp_column, and deletes the first (oldest) one: 

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

ORDER BY may also be useful in some cases to delete rows in an order required to avoid referential integrity violations.

If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:

  1. Select the rows not to be deleted into an empty table that has the same structure as the original table: 

    INSERT INTO t_copy SELECT * FROM t WHERE ... ;

  2. Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name: 

    RENAME TABLE t TO t_old, t_copy TO t;

  3. Drop the original table: 

    DROP TABLE t_old;

No other sessions can access the tables involved while RENAME TABLE executes, so the rename operation is not subject to concurrency problems. See Section 12.1.33, “RENAME TABLE Syntax”.

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 12.2.8.1, “JOIN Syntax”.

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching: 

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

Or: 

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

The preceding examples use INNER JOIN, but multiple-table DELETE statements can use other types of join allowed in SELECT statements, such as LEFT JOIN. For example, to delete rows that exist in t1 that have no match in t2, use a LEFT JOIN: 

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

The syntax allows .* after each tbl_name for compatibility with Access.

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

Note

If you declare an alias for a table, you must use the alias when referring to the table: 

DELETE t1 FROM test AS t1, test2 WHERE ...

Table aliases in a multiple-table DELETE statement should be declared only in the table_references part. Elsewhere in the statement, alias references are allowed but not alias declarations.

Correct: 

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

Incorrect: 

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;

DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;

Declaration of aliases other than in the table_references part can lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. This is such a statement: 

DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;

Before MySQL 5.1.23, alias declarations are allowed in other than the table_references part, but should be avoided for the reason just mentioned.

Cross-database deletes are supported for multiple-table deletes, but you should be aware that in the list of tables from which to delete rows, aliases will have a default database unless one is specified explicitly. For example, if the current database is test, the following statement does not work because the unqualified alias a1 has a default database of test: 

DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2
WHERE a1.id=a2.id;

To correctly match the alias, you must explicitly qualify it with the database of the table being aliased: 

DELETE db1.a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2
WHERE a1.id=a2.id;

User Comments

Posted by Chris Rywalt on January 29 2004 6:07am[Delete] [Edit]

I spent an hour or so working out how to delete rows matching a specific SELECT statement which was mildly complex:

SELECT A.* FROM table1 AS A, table1 AS B
WHERE A.username LIKE '%2'
AND A.ID = B.ID
AND A.username <> B.username

(Basically, I had accidentally created two usernames for each ID, the extra username ending in 2. But there were some valid usernames ending in 2 which I didn't want to delete.)

I tried several different approaches to crafting a delete statement to get rid of these, all to no avail. I tried DELETE...WHERE IN...SELECT and DELETE...WHERE...= ANY...SELECT, WHERE EXISTS, and several other variations, all of which looked like they should work according to the manual, but none of which did.

Finally -- hence this comment, so you don't have to jump through my hoops -- my DBA wife and I put together this solution:

CREATE TEMPORARY TABLE tmptable
SELECT A.* FROM table1 AS A, table1 AS B
WHERE A.username LIKE '%2'
AND A.ID = B.ID
AND A.username <> B.username;

DELETE table1 FROM table1
INNER JOIN tmptable
ON table1.username = tmptable.username;

Maybe this isn't the best way to do this, but it worked for me. Hope it helps someone else.

Posted by Kevin Nelson on April 18 2008 5:27pm[Delete] [Edit]

- Deleting Duplicate Entries -

I had a many-to-many relational table that joined users and events. Some users might save the same event more than once...so I wanted to know a way to delete duplicate entries. The table has a primary key "ueventID" (auto-increment) and two foreign keys "userID" and "eventID". In order to delete duplicate entries, I found that this solution worked quite well for me.

DELETE t1 FROM tbl_name t1, tbl_name t2 WHERE t1.userID=t2.userID AND t1.eventID=t2.eventID AND t1.ueventID < t2.ueventID

This will delete all but the very last entry of the duplicates. If there are any better ways to do this, feel free to let me know. I'll try to remember to check back later.

Honestly, though, while I wanted to know how to do this...officially, I just check to see if it's a duplicate entry BEFORE I insert it so that I don't have to hassle with this :-P

Posted by Linus Lövholm on September 1 2005 7:43am[Delete] [Edit]

Regarding deleting duplicate entries:

I have found two other much more robust ways of doing this, which will accomplish the task even for rows that are complete duplicates.

1) SELECT DISTINCT INTO ...
Perform a select distinct into a new table. Drop the old table. Rename the new table if you want to.

2) Use ALTER IGNORE TABLE and add an index for the duplicate column(s). Given this table (without primary key):

+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
+---+
Do this:

ALTER IGNORE TABLE table1 ADD PRIMARY KEY(a);

Naturally, you can use a UNIQUE index instead of a primary key.

Posted by [name withheld] on December 1 2005 4:13pm[Delete] [Edit]

While it is documented in these pages, it takes a bit of hunting to confirm this incompatible change in v3.23 to v4.1:

If you delete all rows from a table with DELETE FROM tablename, then add some new rows with INSERT INTO tablename, an AUTO_INCREMENT field would start again from 1 using MySQL v3.23.

However, with MyISAM tables with MySQL v4.1, the auto increment counter isn't reset back to 1 - even if you do OPTIMIZE tablename. You have to do TRUNCATE tablename to delete all rows in order to reset the auto increment counter.

This can cause problems because your auto increment counter gets higher and higher each time you do a DELETE all/INSERT new data cycle.

Posted by Radek Maciaszek on August 21 2006 4:30pm[Delete] [Edit]

It's probably worth to mention that DELETE FROM doesn't use the same isolation level in transaction as SELECT. Even if you set isolation level as REPEATABLE READ it doesn't change DELETE behaviour which work as READ COMMITTED. (it affects InnoDB engine in MySQL4 and MySQL5)

Here is an example:

|               User A                 User B
|
|           SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;
|
|           SELECT * FROM t;
|           empty set
|                                 INSERT INTO t VALUES (1, 2);
|
|           SELECT * FROM t;
|           empty set
|                                  COMMIT;
|
|           SELECT * FROM t;
|           empty set
|
|                                  SELECT * FROM t;
|                                  ---------------------
|                                  |    1    |    2    |
|                                  ---------------------
|                                  1 row in set
|                                  
|           DELETE FROM t;
|           Query OK,
|           1 row affected
|//         ^ it delets rows from
|//         outside it's transaction
|                                  
|           COMMIT;
|
|           SELECT * FROM t;
|           empty set

Posted by Luciano Fantuzzi on April 16 2007 3:25am[Delete] [Edit]

Keywords: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails...

I think this is a good practice to do when you're designing a database that has lots of foreign keys. If you have tables with ON DELETE CASCADE option which are linked with other field to other tables, the delete cascade option will fail (because mysql could not delete in the same order you create the tables) with the "ERROR 1452 (23000)". A solution for this case is to declare a clause ON DELETE SET NULL in the others foreign keys. An example:

|-------------------------------------------------------
|  mysql> CREATE TABLE a(
|      -> id INT AUTO_INCREMENT, user VARCHAR(20), PRIMARY KEY(id)) ENGINE=InnoDB;
|  Query OK, 0 rows affected (0.08 sec)
|
|  mysql> CREATE TABLE b(
|      -> id INT AUTO_INCREMENT, id_a INT, name VARCHAR(20), PRIMARY KEY(id),
|      -> FOREIGN KEY(id_a) REFERENCES a(id) ON DELETE CASCADE) ENGINE=InnoDB;
|  Query OK, 0 rows affected (0.08 sec)
|
|  mysql> CREATE TABLE c(
|      -> id INT AUTO_INCREMENT, id_a INT, id_b INT, lastName VARCHAR(20), PRIMARY KEY(id),
|      -> FOREIGN KEY(id_a) REFERENCES a(id) ON DELETE CASCADE,
|      -> FOREIGN KEY(id_b) REFERENCES b(id)) ENGINE=InnoDB;
|  Query OK, 0 rows affected (0.08 sec)
|
|  mysql> INSERT INTO a(user) VALUES('zerocool');
|  Query OK, 1 row affected (0.06 sec)
|
|  mysql> INSERT INTO b(id_a,name) VALUES(1,'carl');
|  Query OK, 1 row affected (0.06 sec)
|
|  mysql> INSERT INTO c(id_a,id_b,lastName) VALUES(1,1,'anderson');
|  Query OK, 1 row affected (0.06 sec)
|
|  mysql> DELETE FROM a WHERE user='zerocool';
|  ERROR 1451 (23000): Cannot delete or update a parent row:
|  a foreign key constraint fails (`apk_zupca/c`, CONSTRAINT
|  `c_ibfk_2` FOREIGN KEY (`id_b`) REFERENCES `b` (`id`))
At this point, the ON DELETE CASCADE is failing because the child table (b) has another FOREIGN KEY (c is linked with b, so row in b can't be deleted). We have created the tables in the correct order, but mysql is trying to delete rows in the order we've created the tables and it's the wrong way. A solution could be the ON DELETE SET NULL. We should add this clause during the creation of the table (or ALTER, if the table is already created):
|  mysql> CREATE TABLE c(
|      -> id INT AUTO_INCREMENT, id_a INT, id_b INT, lastName VARCHAR(20), PRIMARY KEY(id),
|      -> FOREIGN KEY(id_a) REFERENCES a(id) ON DELETE CASCADE,
|      -> FOREIGN KEY(id_b) REFERENCES b(id) ON DELETE SET NULL) ENGINE=InnoDB;
|  Query OK, 0 rows affected (0.08 sec)
And repeating last steps...
|  mysql> INSERT INTO c(id_a,id_b,lastName) VALUES(1,1,'anderson');
|  Query OK, 1 row affected (0.06 sec)
|
|  mysql> DELETE FROM a WHERE user='zerocool';
|  Query OK, 1 row affected (0.06 sec)
|-------------------------------------------------------

Hope be helpful

Posted by Martin Kobele on July 4 2008 12:57pm[Delete] [Edit]

I found a fast way to delete a small subset of rows in a very big table (hundreds of thousands or millions):

You will need the to be deleted IDs in a temporary table which you might already have and you want to delete only those IDs:

A naive way would be to do

DELETE FROM LargeTable WHERE ID IN (SELECT ID FROM TemporarySmallTable);

Given that LargeTable contains maybe 300,000-500,000 and
TemporarySmallTable ca 3,000-6,000 rows, this can take ca 300ms.

Instead, try this:

DELETE FROM LargeTable USING LargeTable INNER JOIN TemporarySmallTable ON LargeTable.ID = TemporarySmallTable.ID;

This DELETE takes on the same database 1ms.

The trick is, that INNER JOIN will 'shrink' the LargeTable down to the size of the TemporarySmallTable and the delete will operate on that smaller set only, since USING will reference to the joined table.

Posted by Omer Faruk EREN on March 10 2009 3:20pm[Delete] [Edit]

I experienced a similiar situation today. I tried this statement:

delete m from members m where membersid in
(
select m.membersid from users u, members m, groups g
WHERE m.usersid=u.usersid AND m.groupsid=g.groupsid and g.groupsname='PARTI' and exists
( SELECT m2.membersid FROM users u2, members m2, groups g2
WHERE m2.usersid=u2.usersid AND m2.groupsid=g2.groupsid and g2.groupsname='MATRAX' and u.usersid=u2.usersid)
);

The Error code was 1093 and explanation was "You can't specify target table 'm' for update in FROM clause". The problem was that members(alias m) table is both the table that i wanted to delete and exists in inner statement. I fund the solution with the temporary table.

Posted by Deepu vs on January 18 2010 12:01pm[Delete] [Edit]

Delete all values in a table including auto increment values using following example

mysql>truncate tablename;


by

Deepu Surendran VS
OCS Technopark


Add your own comment.