mysql删除重复语句问题

[HelloWorldww]

以前用sql server 现在改用mysql数据库，发现同一句语句在sql server里能使用，到了mysql里就报这种错误了[Err] 1093 - You can't specify target table 'KSOthers' for update in FROM clause
sql server 语句如下
DELETE FROM KSOthers
WHERE (id NOT IN
(SELECT MIN(id) AS Expr1
FROM KSOthers AS KS_1
GROUP BY Kid)) or (Kvicinity not like '%昆山市%')
哪位大侠能帮我改成mysql里面的删除重复语句啊

[HelloWorldww]

先顶一下

[education520]

delete from KSOthers where id in
(select id from KSOthers group by kid,id having count(kid) > 1)

[education520]

delete from KSOthers where kid in
(select kid from KSOthers group by kid having count(kid) > 1)

[education520]

先查 看能不能查到你要的结果

[HelloWorldww]

卡卡西 还是报同样的错误，看来这就是mysql 独特的sql 语法所致 上网一查说要把子查询当成个临时表启个别名再套一层，my god!

[HelloWorldww]

[Quote=引用 8 楼 的回复:]
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from……
[/Quote]

还是同样的错误 [Err] 1093 - You can't specify target table 'ksothers' for update in FROM clause

[HelloWorldww]

怎么没人回复啊 看来只能自己解决了
create table tmp as SELECT Min(id) AS Expr1 FROM KSOthers AS KS_1 GROUP BY Kid;
DELETE FROM ksothers WHERE(id NOT IN (select Expr1 from tmp)) or (Kvicinity not like '%昆山市%');
drop table tmp

[HelloWorldww]

求高手整合成一条语句

[test2050]

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

http://unix-cd.com/vc/www/26/2007-07/6099.html

[@信心]

8楼正解。

[test2050]

http://blog.csdn.net/wangts/article/details/3665296
看看有没有帮助

[test2050]

http://www.zhongsisi.com/mysql-error-1093-hy000-solution/

[lcqy84_1]

[Err] 1093 - You can't specify target table 'ksothers' for update in FROM clause

[lcqy84_1]

MYsql删除重复数据 报错解决方案：
[Err] 1093 - You can't specify target table 'ksothers' for update in FROM clause

解决方案：
例如表
INSERT INTO `dome_1` (`id`, `xuehao`, `types`, `fenshu`) VALUES (1, 'wangwu', '语文', '80');
INSERT INTO `dome_1` (`id`, `xuehao`, `types`, `fenshu`) VALUES (2, 'zhangsan', '语文', '50');
INSERT INTO `dome_1` (`id`, `xuehao`, `types`, `fenshu`) VALUES (3, 'wangwu', '语文', '60');
INSERT INTO `dome_1` (`id`, `xuehao`, `types`, `fenshu`) VALUES (4, 'zhangsan', '语文', '80');
INSERT INTO `dome_1` (`id`, `xuehao`, `types`, `fenshu`) VALUES (5, 'wangwu', '语文', '39');
INSERT INTO `dome_1` (`id`, `xuehao`, `types`, `fenshu`) VALUES (6, 'lisi', '数学', '69');

解决范例：SQL
DELETE dome_1 AS a from dome_1 AS a,
(SELECT *,MIN(id) FROM dome_1 group by xuehao,types having count(*) > 1) AS c
WHERE a.xuehao=c.xuehao AND a.types = c.types AND a.id NOT IN (c.id)

[lcqy84_1]

才看到你的问题帖子，不知道是否现在还能否帮上