MySQL中删除重复记录

DELETE FROM users
WHERE NAME  IN (SELECT  NAME
                          FROM users
                          GROUP  BY  NAME
                          HAVING  COUNT(NAME) > 1)
     AND
     id NOT IN (SELECT MIN(id)
                       FROM  users
                       GROUP BY NAME
                       HAVING COUNT(NAME)>1)

这样写的话会提示
Error Code : 1093
You can't specify target table 'users' for update in FROM clause

改成>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

DELETE users AS a FROM users AS a,
(
SELECT *,MIN(id) FROM users GROUP BY name HAVING COUNT(1) > 1
) AS b
 WHERE a.name = b.name AND a.id > b.id;

posted on 2009-08-09 17:40 想飞就飞 阅读(309) 评论(0)  编辑  收藏 所属分类: 开发工具&环境


只有注册用户登录后才能发表评论。


网站导航:
 

公告


导航

<2009年8月>
2627282930311
2345678
9101112131415
16171819202122
23242526272829
303112345

统计

常用链接

留言簿(13)

我参与的团队

随笔分类(69)

随笔档案(68)

最新随笔

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜