如何从SQL表中删除重复数据 [英] How do I delete duplicate data from SQL table

问题描述

我正在从第三方来源的数据上传和更新我的数据库.不幸的是，来自第三方数据源的数据中有很多重复记录.

I am in the midst of uploading and updating my db from data from a third party source. Unfortunately, there are many duplicate records in the data from the third party data source.

我在这里查看了一些关于 SO 的问题，但所有这些问题似乎都是有一个 ID 列将一行与另一行区分开来的情况.

I looked at a few questions here on SO but all of them seem to be cases where there is an ID column which differentiates one row from the other.

就我而言，没有 ID 列.例如

In my case, there is no ID column. e.g.

State   City    SubDiv  Pincode Locality Lat    Long
Orissa  Koraput Jeypore 764001  B.D.Pur 18.7743 82.5693
Orissa  Koraput Jeypore 764001  Jeypore 18.7743 82.5693
Orissa  Koraput Jeypore 764001  Jeypore 18.7743 82.5693
Orissa  Koraput Jeypore 764001  Jeypore 18.7743 82.5693
Orissa  Koraput Jeypore 764001  Jeypore 18.7743 82.5693

是否有一个简单的查询可以运行以删除所有重复记录并保留一条记录作为原始记录?因此，在上述情况下，我想从表中删除第 3、4、5 行.

Is there a simple query which I can run to delete all duplicate records and keep one record as the original? So in the above case I want to delete rows 3,4,5 from the table.

我不确定这是否可以使用简单的 sql 语句来完成，但想知道其他人的意见如何做到这一点

I am not sure if this can be done using simple sql statements but would like to know others opinion how this can be done

推荐答案

我会将第三方数据插入临时表，然后:

I would insert the third party data to a temporary table that then:

insert into
  target_table
select distinct
  *
from
  temporary_table

最后删除临时表.

只有不同(唯一)的行才会被插入到目标表中.

Only distinct (unique) rows will be inserted to the target table.

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