如何根据某个字段从数据库表中删除重复项 [英] how to delete duplicates from a database table based on a certain field

问题描述

我有一张桌子，有些复制了。我基本上想删除所有重复记录，这是由我的表中称为SourceId的字段定义的。每个源代码只能有一条记录。

是否有任何可以写入的SQL会删除每个副本，所以我每个Sourceid只有一个记录。 p>

解决方案

假设你有一个可以打破重复的源代码的列ID，你可以使用它。使用 min（id）使它只保留每个源代码批次的最小值（id）。

 从tbl 
中删除，其中id不在
（
中选择min（id）
 from tbl 
 group by sourceid 
）
  

i have a table that somehow got duplicated. i basically want to delete all records that are duplicates, which is defined by a field in my table called SourceId. There should only be one record for each source ID.

is there any SQL that i can write that will delete every duplicate so i only have one record per Sourceid ?

解决方案

Assuming you have a column ID that can tie-break the duplicate sourceid's, you can use this. Using min(id) causes it to keep just the min(id) per sourceid batch.

delete from tbl
where id NOT in
(
select  min(id)
from tbl
group by sourceid
)

这篇关于如何根据某个字段从数据库表中删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！