mysql like消除_mysql 删除多余重复记录

(stu表中的名字(name)是重复的)

问题：

在Mysql下执行：

delete from stu where name in (select name from stu group

by name having count(name) > 1) and id not in (select id from stu group by

name having count(name) > 1);

MySql报错： You can't specify target table 'table name' for update in FROM

clause

在

Mysql下执行：

select id from stu where name in (select name from stu group

by name having count(name) > 1) and id not in (select id from stu group by

name having count(name) > 1);

是成功的。

注意：上面的两句话很矛盾啊。

就现在的mysql版本(我用5.1)来说，不能先select出同一表中的某些值，再update这

个表(在同一语句中)

解决：

create table tmp_table as select id

from stu where name in (select name from stu group

by name having count(name) > 1) and id not in (select id from stu

group by

name having count(name) > 1);

delete from stu where id in (select id from tmp_table);

drop table tmp_table;

0

顶

0

踩

分享到：

2010-03-25 00:30

浏览 1418

分类:数据库

评论