MySQL基础：假删除重复数据，并保留重复数据中的一条数据_假删除的数据是否需要放在重复数据判断中-CSDN博客

本文链接：https://blog.csdn.net/starryasky/article/details/83211477

按照正常思路：先获取重复数据进行筛选，然后再进行假删除。但是报错：You can't specify target table 'i' for update in FROM clause

1. 查询数据：

首先查询出重复记录，按name字段分组：

SELECT * , COUNT(*) as counts FROM students m GROUP BY m.NAME HAVING counts >1

然后筛选出要做假删除的数据：

条件是：查询出的 id字段不在上述查询里面但 name字段相同数据；即查询重复数据并保留其中的一条数据

SELECT * FROM students i
WHERE i.name IN(SELECT s.name FROM students s GROUP BY s.NAME HAVING COUNT(*)>1)
AND i.id NOT IN(SELECT m.id FROM students m GROUP BY m.NAME HAVING COUNT(*)>1 ) ;

2. 假删除数据：

正常思路，直接修改标志

UPDATE students i SET i.sign = -1 
WHERE i.name IN(SELECT s.name FROM students s GROUP BY s.NAME HAVING COUNT(*)>1)
AND i.id NOT IN(SELECT m.id FROM students m GROUP BY m.NAME HAVING COUNT(*)>1 ) ;

但是执行后报错：You can't specify target table 'i' for update in FROM clause

查看MySQL官方文档，原因是：In general, you cannot modify a table and select from the same table in a subquery；即不能在子查询中修改表并从同一表中进行选择。但是可以使用派生表（原因是：在这里派生表的结果被具体化为一个临时表）。（具体查看这里------MySQL文档链接）

最后MySQL语句修改为，修改成功：

UPDATE students i SET i.sign = -1 
WHERE i.name IN(
    SELECT stua.name FROM(
        SELECT s.name FROM students s GROUP BY s.NAME HAVING COUNT(*)>1
    )stua
)
AND i.id NOT IN(
    SELECT stub.id FROM (
        SELECT m.id FROM students m GROUP BY m.NAME HAVING COUNT(*)>1 
    )stub
) ;