今天需要删除一个表中的重复数据,先查询出重复的数据,sql:
select * from tpf.order_unused where airTicket in(select airTicket from order_unused GROUP BY airTicket having count(id)>1)
and id not in(SELECT min(id) from order_unused GROUP BY airTicket having count(id)>1);
然后本来向直接将select * 换成delete 就可以了,但是执行后出现错误,上网查了一下,发现是因为mysql不支持
不能先select出同一表中的某些值,再update这个表(在同一语句中)所以需要使用临时表,sql 如下:
create table tmp_order_unused as select id from tpf.order_unused where airTicket in(select airTicket from order_unused GROUP BY airTicket having count(id)>1)
and id not in(SELECT min(id) from order_unused GROUP BY airTicket having count(id)>1);
先将重复的数据id放到一个临时表中,再通过临时表把本表中的对应id的数据删除即可:
delete from tpf.order_unused where id in(select id from tmp_order_unused)