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 12.14, “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.

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.11.1.5, “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”.

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
(such as MyISAM, MEMORY,
and MERGE).

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

The IGNORE modifier causes MySQL to ignore
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 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:

Create a table that contains an indexed
AUTO_INCREMENT column.

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

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:

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

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

You can specify multiple tables in a
DELETE statement to delete rows
from one or more tables depending on the condition in the
WHERE clause. You cannot use ORDER
BY or LIMIT in a multiple-table
DELETE. The
table_references clause lists the
tables involved in the join, as described in
Section 13.2.9.2, “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:

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 permitted 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 permits .* 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 should be declared only in
the table_references part of the
statement. Elsewhere, alias references are permitted 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;

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:

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

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

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

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
on
December 1, 2005

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

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)

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:

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):

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

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 gWHERE m.usersid=u.usersid AND m.groupsid=g.groupsid and g.groupsname='PARTI' and exists( SELECT m2.membersid FROM users u2, members m2, groups g2WHERE 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

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

mysql>truncate tablename;

by

Deepu Surendran VSOCS Technopark

Posted by
Jim Leek
on
September 8, 2010

Deleting an individual duplicate entry.===========================Say you have a table with two identical entries:

DELETE FROM sessionEventLog WHERE sessionEventLog.sessionID IN(SELECT sessions.sessionID FROM sessions WHERE sessions.sourceID = 6);

Posted by
Aleksey Midenkov
on
November 1, 2012

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