MySQL 删除表中重复的记录（where......）

最新推荐文章于 2023-07-07 07:51:00 发布

转载最新推荐文章于 2023-07-07 07:51:00 发布 · 1.3k 阅读

文章标签：

#mysql #delete #subquery #table #exception #forms

数据库专栏收录该内容

11 篇文章

订阅专栏

本文介绍了MySQL中如何处理表内的重复记录，包括单字段和多字段重复情况的处理方法，并给出了具体的SQL语句示例。此外，还讲解了MySQL中DELETE和UPDATE操作的特殊用法及注意事项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

delete from at1 where id in (select * from (select max(id) from at1 group by str having count(str) > 1) as b);

delete a from at1 as a, at1 as b where a.str=b.str and a.id > b.id;

3. 多个字段重复的问题。

“name”值存在重复的项； Select Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

（见http://hi.baidu.com/yangjifu/blog/item/723e5f4cf92708ffd72afc92.html）

-----------------------------------------

1.MySQL的delete 支持：

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

2. 从 MySQL 4.0.4 开始，你也可以执行一个包含多个表的 UPDATE 的操作：

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

注意：多表 UPDATE 不可以使用 ORDER BY 或 LIMIT。

3. 查询一个表中重复的记录：

select max(id) from at1 group by str having count(str) > 1

select a.id from at1 as a, at1 as b where a.str=b.str and a.id > b.id

4. delete from at1 where id in (select max(id) from at1 group by str having count(str) > 1); 是错误的：

ERROR 1093 : You can't specify target table 'at1' for update in FROM clause

正确写法：delete from at1 where id in (select * from (select max(id) from at1 group by str having count(str) > 1) as b);

理由（文档）：

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:

UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

Here the prohibition does not apply because a subquery in the FROM clause is materialized as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.

mysql还支持 where (a,b) in (select a, b from aaaa)