今天遇到删除表中重复的问题。pid为主键,但想要删除menuid相同重复记录。刚开始想到的语句是:
DELETE from qb_product where id not in(SELECT id=min(id) from qb_product GROUP BY menuid)
但MySQL不肯干,You can't specify target table 'blur_article' for update in FROM clause
也就是说你不能边查边删啊。后来想如果这样,那我全查出来再删呗。
用了如下方法,成功了。记录一下。
create table tmp as select min(pid) as col1 from qb_product group by menuid;
delete from qb_product where pid not in (select col1 from tmp);
drop table tmp;
创建一个临时表,用完就删除掉。