Additional languages
« 13.2 Data Manipulation Statements
13.2.2 DO Syntax »
Section Navigation      [Toggle]

13.2.1. 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 the number of rows deleted. 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 13.2.7, “SELECT Syntax”.

As stated, a DELETE statement with no WHERE clause deletes all rows. A faster way to do this, when you do not want to know the number of deleted rows, is to use TRUNCATE TABLE. See Section 13.2.9, “TRUNCATE 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 14.5.6.3, “How AUTO_INCREMENT Columns Work 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.

  • 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.19, “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, but myisamchk is faster. See Section 13.5.2.5, “OPTIMIZE TABLE Syntax”, and Section 8.5, “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, the rows are deleted in the order specified by the clause. This is really useful only 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;

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 13.2.7.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, 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;

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 show inner joins that use the comma operator, but multiple-table DELETE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

The syntax allows .* after the table names 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 provide an alias for a table, you must use the alias when referring to the table: 

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

Cross-database deletes are supported for multiple-table deletes, but in this case, you must refer to the tables without using aliases. For example: 

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...

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


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 mmj on February 14 2004 2:25am[Delete] [Edit]

> If you issue a DELETE with no WHERE clause, all rows are
> deleted. If you do this in AUTOCOMMIT mode, this works as
> TRUNCATE. See section 13.1.9 TRUNCATE Syntax.

Note, however, that TRUNCATE will not work if you have a lock on the table, because a TRUNCATE involves dropping and re-creating the table.

If you do a DELETE with no WHERE clause on a locked table, it seems that MySQL is intelligent enough to realise this, because it still works, even though the equivalent TRUNCATE would not because of the lock. In this case MySQL must not be translating it to a TRUNCATE.

Posted by mike on February 24 2004 4:43am[Delete] [Edit]

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

Actually that is not the only time it is useful. For one thing, you can use this to make sure that the deletes happened in an order which preserves referential integrity.

If you have a table that has rows which are parents of other rows, and you know that you have created them in such a way that the parent rows are always created before the child rows, you could use the order by in your delete to get the child rows first.


mysql> delete from account where 1>0;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
mysql> select * from account;

+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
|  1 |      NULL | mike |
|  2 |         1 | reid |
|  3 |         1 | cole |
+----+-----------+------+
3 rows in set (0.00 sec)

mysql> delete from account where 1>0 order by id desc;
Query OK, 3 rows affected (0.04 sec)


Posted by [name withheld] on September 24 2004 9:09am[Delete] [Edit]

(In addition to Chris Rywalts explanation which did not work on my computer (MYSQL 4.0.12-nt))
If you want to delete multiple columns at once matching
a more complex select clause use the follwing syntax:

CREATE TEMPORARY TABLE tmptable
SELECT t1.* FROM table1 as t1
LEFT JOIN table2 as t2 ON t2.id = t1.id
WHERE t2.id is NULL;

DELETE FROM table1 USING tmptable, table1
WHERE table.id = tmptable.id;

This statement will delete all rows that exist in table1 but
do not have a corresponding entry in table2

Posted by Kevin Nelson on November 2 2004 6:21pm[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 tbl_name 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 Kris White on December 2 2004 3:22pm[Delete] [Edit]

The docs say that you can use any joins within the delete statement, but I'm not finding that it works on 4.1.3-nt

Both of these statements will execute SELECTS properly:

select lineid from answersgiven as a left join purchasehistory as b on a.purchaseid=b.purchaseid where questionid in (select questionid from questions where chapterid<>99) and b.CourseStatus='Completed')
or
select LineID from answersgiven as a
left join purchasehistory as b on a.purchaseid=b.purchaseid
left join questions as c on a.questionid=c.questionid
where c.chapterid<>99 and b.CourseStatus='Completed'

And I get the rows that will be affected. However, when I try to change this to a DELETE, i.e.

delete from answersgiven as a left join purchasehistory as b on a.purchaseid=b.purchaseid where questionid in (select questionid from questions where chapterid<>99) and b.CourseStatus='Completed')

it errors out on the join command. I've also tried wrapping the working SELECT inside a DELETE, such as

delete from answersgiven where lineid in (select lineid...)

But it also fails.

Posted by wendy reetz on January 8 2005 8:06pm[Delete] [Edit]

You shouldn't have to use a temporary table when doing left joins in delete statements.

This worked for me, note the "Using" command.

delete from table1 using table1 left join table2 on ON t2.id = t1.id WHERE t2.id is NULL;

Posted by ben johnson on February 11 2005 7:32pm[Delete] [Edit]

perhaps I missed this example above. I'm rather new to mysql, and somethings are still very unclear to me.

What I needed to do was allow a member to manage their journal. In this case, remove their journal from the db. Each journal has two tables that hold information for it. Each table is tied by the journal id. I wanted to remove both of these tied rows in a single query, and this is what I found would work:

DELETE journal_t1, journal_t2
FROM journal_t1
LEFT JOIN journal_t2
ON journal_t1.key = journal_t2.key
WHERE journal_t1.key = '800001'

in this example, the user is removing journal entry 800001. there is more to this query (such as a userid comparison), but I didnt feel including that was necessary for this example. Hope this helps someone. it's saving me a lot of hassle now.

Posted by ben johnson on February 11 2005 7:57pm[Delete] [Edit]

another example (expanding on [name withheld]'s on 1/26/04)
I thought something a bit more specific (for my own needs anyway) would help a few people.

I'm allowing a user to delete bulk "viewers" from their "view list". I didn't want to do multiple queries for each removal, and I wanted to add another "where" to cross check for the "view lists" OWNER_ID. (this would prevent any malicious posts (hopefully)).
(the table is designed as such: UNIQUEID_ROW, OWNER_ID, VIEWER_ID)

Here it is, hope it helps someone:
===========================
DELETE FROM view_table
WHERE OWNER_ID='this_users_id'
AND VIEWER_ID IN (10,11,20,13,16)";
===========================

this will remove rows with VIEWER_ID equal to 10,11,20,13, & 16, IF and only if the OWNER_ID in those rows matches as well. I'm not too sure what "IN" means (^^), but this query seems to work perfectly.

Posted by Alexandre Nunes on August 7 2005 4:55pm[Delete] [Edit]

I just successfully tried this to delete all records on table1 without the corresponding key on table2, without requiring a temporary table:

delete table1 from table1 left join table2 on table1.key = table2.key WHERE table2.key IS NULL;

It worked on mysql 4.1.13 (debian), not sure about older ones but i guess it's ok.

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 Stefan Hanssen on September 9 2005 11:33am[Delete] [Edit]

Deleting entries in one table that don't have any links in another table seems to be a popular topic :)
I've done my own struggling with this:
In the database there is a table image and a table imagename.
Table imagename links to image by imagename.image=image.aindex.
I wanted to delete all entries from image, that are not refered to in imagename. Using a left join is not feasable, since the data is too large (217174 entries in image, 198744 in imagename).
The steps i took that finally did the trick was:

1) create a table, containing the distinct
reference numbers
CREATE TEMPORARY TABLE distinct_images
SELECT DISTINCT imagename.image FROM imagename;

2) create an extra field in table image, and tag
the entries in image that are referenced
ALTER TABLE image ADD linked tinyint default 0;
UPDATE image i, distinct_images di SET i.linked=1
WHERE i.aindex=di.image;

3) delete the entries from image that are not tagged
DELETE FROM image where linked=0;

4) delete the tagging field
ALTER TABLE image DROP COLUMN linked;

Quite a workaround, but it worked (199370 entries got
deleted in no time).

Posted by Alex Shannon on September 27 2005 3:44pm[Delete] [Edit]

I tried all of the above to try and delete rows in one table that didn't exist in another. I was trying:

DELETE FROM task_printers USING
task_printers t LEFT JOIN workstation_details w
ON t.taskid = w.taskid
WHERE w.taskid IS NULL

The solution was to use the alias instead of the actual table name:

DELETE FROM t USING
task_printers t LEFT JOIN workstation_details w
ON t.taskid = w.taskid
WHERE w.taskid IS NULL

Hope it helps someone.

Posted by cordial Camaraderie on October 26 2005 10:15am[Delete] [Edit]

This should be more eloborate. It good for a fresher bot not for intermediate level.

Thank You
----------------
Data Recovery Software & Services
http://www.stellarinfo.com

Posted by Flemming Funch on November 5 2005 11:31pm[Delete] [Edit]

I'm sometimes puzzled by the time a delete of many rows will take in a big table. Like, I have a table with a couple of million records and each day I need to delete a days worth of records, which is 6000 or so. If I do it with one delete statement, it takes a couple of minutes, and locks the table in the meantime, which is a big problem. If instead I write a PHP program that does a select of the records and does a one by one low_priority delete of each one, it takes about a minute and a half total, and only locks the table for a short period while doing the select. Not what one would have guessed, but maybe somebody else can use the tip that it isn't always fastest to do the most direct and simple unified sql statement.

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 Urko Benito on October 10 2006 3:33pm[Delete] [Edit]

Yo can delete the rows from one table (t1) not joined to the rows of other table (t2) his way:

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

Add your own comment.