Mysql删除重复数据只保留一条

[sinat_19250161]

Mysql删除重复数据只保留一条，我的sql这样的
DELETE from t_patient WHERE pa_iid IN (select pa_iid from t_patient group by pa_vname having count(pa_vname)>1)
但是报错了：
[Err] 1093 - You can't specify target table 't_patient' for update in FROM clause
求大神，怎么解决

[千年丶月祭]

用 rowid 去删除。比如 delete from TB_PERIOD where CGAMEID =20 and CPERIODID < 150516001 and rowid not in (select min(rowid) from TB_PERIOD group by CPERIODID)

[sinat_19250161]

引用 1 楼 empty6027 的回复:

用 rowid 去删除。比如 delete from TB_PERIOD where CGAMEID =20 and CPERIODID < 150516001 and rowid not in (select min(rowid) from TB_PERIOD group by CPERIODID)

不行 报错： [Err] 1054 - Unknown column 'rowid' in 'field list'

[海贼小D]

select s.*,@rownum:=@rownum+1 as rownum from pension_service s,(select @rownum:=0) a

排序之后删掉rownum > 1的就好了

[海贼小D]

我上面只是一个rownum的例子，之前用的

[sinat_19250161]

引用 3 楼 rudy1245 的回复:

select s.*,@rownum:=@rownum+1 as rownum from pension_service s,(select @rownum:=0) a 排序之后删掉rownum > 1的就好了

@rownum:=@rownum+1 这个是什么？能详细说下吗？

[海贼小D]

这就是一个临时变量，根据记录条数自动加1，这样就可以得到序号。你想删除几条条记录，就可以根据这个变量来加条件了。

[sinat_19250161]

引用 6 楼 rudy1245 的回复:

这就是一个临时变量，根据记录条数自动加1，这样就可以得到序号。你想删除几条条记录，就可以根据这个变量来加条件了。

那是不是还要写存储过程啊 ？

[海贼小D]

不用啊，你删除的时候就可以筛选结果。

[海贼小D]

如果是多条记录，可能要循环了。

[abc130314]

DELETE s1
FROM t_patient s1
LEFT JOIN (
SELECT MIN(pa_iid) id
FROM t_patient
GROUP BY pa_vname) s2 ON s1.pa_iid = s2.id
WHERE s2.id IS NULL

[luoyihu]

引用 10 楼 abc130314 的回复:

DELETE s1
FROM t_patient s1
LEFT JOIN (
SELECT MIN(pa_iid) id
FROM t_patient
GROUP BY pa_vname) s2 ON s1.pa_iid = s2.id
WHERE s2.id IS NULL

子查询里面不用写having吗？

having COUNT(pa_vname) > 1

[luoyihu]

引用 10 楼 abc130314 的回复:

DELETE s1
FROM t_patient s1
LEFT JOIN (
SELECT MIN(pa_iid) id
FROM t_patient
GROUP BY pa_vname) s2 ON s1.pa_iid = s2.id
WHERE s2.id IS NULL

看懂了，算我没说。。。