问题:
去除MySQL中重复的记录,仅保留其中一条。本例中cname为重复项,而cid为primary key。
尝试解决:
使用代码:
DELETE FROM t_customer WHERE cname in ( SELECT cname FROM t_customer GROUP BY cname HAVING count(cname)>1 ) AND cid NOT IN ( SELECT min(cid) cid FROM t_customer GROUP BY cname HAVING count(cname)>1 )
代码思路:
设置2个条件,1是查出cname相同的记录里的cname值,2是查出cname相同的记录里cid最小的一项记录的cid值,然后据此进行删除cname重复的记录,保留其中cid最小的记录。
运行以后发现:
报错:[HY000][1093] You can't specify target table 't_customer' for update in FROM clause
报错原因:(找了几篇博客看来的)大意是:不能在同一条语句中对某个字段进行判断的同时,又对其进行update操作。有博主还提到“这个问题只出现于MySQL,MSSQL和Oracle不会出现此问题”(未验证过,在此只是提一句)
最后解决:
1种代码:
DELETE FROM t_customer
WHERE cname in
(
SELECT cname FROM
(
SELECT cname FROM t_customer GROUP BY cname HAVING count(cname)>1
) t1
)
AND cid NOT IN
(
SELECT cid FROM
(
SELECT min(cid) cid FROM t_customer GROUP BY cname HAVING count(cname)>1
) t2
)
思路:
将之前的2个查询结果先暂存为t1和t2,然后从这2个临时集合中查询出cname和cid作为删除条件
第2中代码:
看其他的博客看到的:
DELETE t_customer
FROM
t_customer,
(
SELECT min(cid) cid, cname
FROM t_customer
GROUP BY cname
HAVING count(cname)>1
)t
WHERE t_customer.cname=t.cname
AND t_customer.cid>t.cid;
运行以后也得到了想要的结果。