删除重复记录而不创建临时表 [英] Delete duplicate records without creating a temporary table

问题描述

我有一个包含许多重复记录的表:

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

推荐答案

这是一个就地解决方案(但不是单一方案)

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

找出最大ID:

select max(id) as maxid 
  from shop;

记住该值.假设它等于1000；

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;

丢弃旧值:

delete from shop
  where id <= 1000;

还原普通ID:

update shop
  set id = id - 1000;

利润！

这篇关于删除重复记录而不创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！