DELETE
Syntax
1063
No other sessions can access the tables involved while
RENAME TABLE
executes, so the rename
operation is not subject to concurrency problems. See
Section 13.1.20, “
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 13.2.8.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:
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 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. Declaration of aliases other than in the
table_references
part should be avoided
because that can lead to ambiguous statements that have unexpected results such as deleting rows
from the wrong table. This is such a statement:
DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;
For alias references in the list of tables from which to delete rows in a multiple-table delete, the default
database is used unless one is specified explicitly. For example, if the default database is
db1
, the
following statement does not work because the unqualified alias reference
a2
is interpreted as having a
database of
db1
:
DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2
WHERE a1.id=a2.id;
To correctly match an alias that refers to a table outside the default database, you must explicitly
qualify the reference with the name of the proper database:
DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...