delete select 表

为什么80%的码农都做不了架构师？>>>   

清理表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;

转载于:https://my.oschina.net/neo600/blog/77023