清理表tb1中的数据,name相同的项只保留一个(以下讨论只保留id值最大即最新的一个)
delete from tb1 where id in (select max(id) from tb1 group by name);
这段代码在mysql中会出错,提示 you can't specify target table 'tb1' for update in from clause 错误,意思大概是不能对正在打开作为条件的表进行修改。解决方法:建立临时表
create table tmp as select max(id) as col1 from tb1 group by name;
delete from tb1 where id not in (select col1 from tmp);
drop table tmp;