delete duplicate rows (and leaving one copy) in PostgreSQL, MS SQL, MySQL

MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK


delete from product where
seq_id not in (select min(seq_id) from product group by name)

to make this work with MySQL:

delete from product where
seq_id not in (select x from (select min(seq_id) as x from product group by name))




MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK

delete from product where
exists(select * from product x where x.name = product.name and product.seq_id > x.seq_id)



MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK

/* advantage: can re-use existing query's logic for finding the first record of each duplicate */

delete from product where
exists(
select x.name, min(x.seq_id)
from product x
where x.name = product.name
group by x.name
having product.seq_id > min(x.seq_id)
);


MS SQL: OK, PostgreSQL: NOT OK, MySQL: OK

delete product from product
inner join product b
on b.name = product.name and product.seq_id > b.seq_id;

MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK


delete from product where
seq_id in
(select a.seq_id from product a inner join product b on a.name = b.name and a.seq_id > b.seq_id);

to make this work with MySQL:

delete from product where
seq_id in
(select x from (select a.seq_id as x from product a inner join product b on a.name = b.name and a.seq_id > b.seq_id));

/* MySQL is pretty lame in this last case */