Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROMtbl_name
[WHEREwhere_condition
] [ORDER BY ...] [LIMITrow_count
]
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]tbl_name
[.*] [,tbl_name
[.*]] ... FROMtable_references
[WHEREwhere_condition
]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROMtbl_name
[.*] [,tbl_name
[.*]] ... USINGtable_references
[WHEREwhere_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 TABLE
Syntax”, and
Section 12.3.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
(without a
tbl_name
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
(such as MyISAM
, MEMORY
,
and 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.4.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:
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
option to
row_count
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:
RENAME TABLE t TO t_old, t_copy TO t;
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.
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 default 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
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.
- 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
Regarding deleting duplicate entries:
Do this: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):
ALTER IGNORE TABLE table1 ADD PRIMARY KEY(a);
Naturally, you can use a UNIQUE index instead of a primary key.
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.
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:
Keywords: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails...
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):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:
And repeating last steps...
Hope be helpful
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.
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.
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.