删除重复记录而不创建临时表

Question

I have a table with many duplicate records:

shop
ID     tax_id
1      10
1      10
1      11
2      10
2      12
2      10
2      10

I want to delete all duplicate records without creating a temporary table. After the update query, the table should look like:

shop
ID     tax_id
1      10
1      11
2      10
2      12

Answer 1

Here's an in-place solution (but not one-liner)

Find out max id:

select max(id) as maxid 
  from shop;

Remember this value. Let's say it equals to 1000;

Re-insert unique values, with offset:

insert into shop (id, tax_id) 
select distinct id + 1000, tax_id 
  from shop;

Drop old values:

delete from shop
  where id <= 1000;

Restore normal ids:

update shop
  set id = id - 1000;

PROFIT!