赞
踩
Problem: Write a SQL query to delete all duplicate email entries in a table named Person
, keeping only unique emails based on its smallest Id.
- +----+------------------+
- | Id | Email |
- +----+------------------+
- | 1 | john@example.com |
- | 2 | bob@example.com |
- | 3 | john@example.com |
- +----+------------------+
For example, after running your query, the above Person
table should have the following rows:
- +----+------------------+
- | Id | Email |
- +----+------------------+
- | 1 | john@example.com |
- | 2 | bob@example.com |
- +----+------------------+
GROUP BY
- mysql> select * from Person;
- +------+------------------+
- | Id | Email |
- +------+------------------+
- | 1 | john@example.com |
- | 2 | bob@example.com |
- | 3 | john@example.com |
- +------+------------------+
- 3 rows in set (0.00 sec)
-
- mysql> select id from Person group by email;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
- 2 rows in set (0.00 sec)
-
- mysql> delete from person where id not in(select id from Person group by email);
- ERROR 1093 (HY000): You can't specify target table 'person' for update in FROM clause
?Subqueries: You cannot delete from a table and select from the same table in a subquery.
若 DELETE
语句中包含 ORDER BY
子句, 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
also helps to delete rows in an order required to avoid referential integrity violations.
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 Clause”.
There are 2 types of multiple-table 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.di AND t2.id=t3.id;
-
- DELETE FROM t1,t2 USING t1 INNER JOIN t2 INNER JOIN t3
- WHERE t1.id=t2.di 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;
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.
- 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;
- SQL92: DELETE p1 FROM Person p1,Person p2 WHERE p1.Email=p2.Email AND p1.Id>p2.Id;
-
- SQL99: DELETE p1 FROM Person p1 INNER JOIN Person p2 ON p1.Email=p2.Email AND p1.Id>p2.ID;
SQL92(1648ms)语法的执行速度优于SQL99(2194ms),不知为何?
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。