问题:表部分字段重复,需要对部分字段重复的数据,保留一条,其余删除。
1 张三 河北 12 河北唐山
2 李四 河南 20 河南郑州
3 王五 山西 10 山西太原
4 赵六 山东 31 山东济南
5 张三 河北 25 河北邢台
6 李四 山东 16 山东青岛
7 王五 湖北 45 湖北武汉
8 赵六 山东 49 山东菏泽
9 张三 河北 55 河北秦皇岛
需求:如图person表中姓名和省份字段有重复数据,需要删除;
第一步,查询下重复的数据:
SELECT MIN(id) AS minId,MAX(id) AS maxId,`name`,province,COUNT(*)
FROM person
GROUP BY `name`, province
HAVING COUNT(*) > 1;
结果如下:
第二步,将重复数据存储为一个临时表;
CREATE TABLE person_temp (SELECT MIN(id),MAX(id),`name`,province,COUNT(*)
FROM person
GROUP BY `name`, province
HAVING COUNT(*) > 1);
第三步,查询出要删除的person表的数据id【因为重复的数据不止有2条,此处保留id最小的数据,删除id较大的数据;亦可以保留id最大的数据】;
SELECT p.*
FROM person p,person_temp pt
WHERE p.`name`=pt.`name`
AND p.province=pt.province
AND p.id != pt.minId;
得到要删除的数据如下:
第四步,执行删除
DELETE FROM person
WHERE id
IN (
SELECT p.id
FROM person p,person_temp pt
WHERE p.`name`=pt.`name`
AND p.province=pt.province
AND p.id != pt.minId
);
执行发现报错了,
[SQL]DELETE FROM person
WHERE id IN (
SELECT p.id
FROM person p,person_temp pt
WHERE p.`name`=pt.`name`
AND p.province=pt.province
AND p.id != pt.minId);
[Err] 1093 - You can't specify target table 'person' for update in FROM clause
You can't specify target table 'brand' for update in FROM clause
意思就是:不能对同一个表(person)执行先查询在删除操作;
这种操作在oracle数据库是没有问题的。替换方案就是先建一个临时表,再删除;
创建临时表:
CREATE TABLE person_id_temp(
SELECT p.id
FROM person p,person_temp pt
WHERE p.`name`=pt.`name`
AND p.province=pt.province
AND p.id != pt.minId
);
执行删除:
DELETE FROM person WHERE id IN (SELECT id FROM person_id_temp);
最终数据如下:
1 张三 河北 12 河北唐山
2 李四 河南 20 河南郑州
3 王五 山西 10 山西太原
4 赵六 山东 31 山东济南
6 李四 山东 16 山东青岛
7 王五 湖北 45 湖北武汉