如何从Oracle数据库中删除重复的行？

Question

我们有一个表，由于意外原因，相同的数据被插入了两次，这意味着大多数行（但不是全部）在表中出现了两次。简单地说，我想要一条SQL语句来删除一行的一个版本，同时保留另一个版本；我不介意删除哪个版本，因为它们是相同的。

表结构大致如下：

FID，unique_ID，COL3，COL4....

Unique_ID是主键，每个值只出现一次。 FID是对于每个要素都唯一的键，因此如果它出现多次，则应删除重复项。

选择具有重复项的要素的语句是：

select count(*) from TABLE GROUP by FID

不幸的是，我无法从中得出一条SQL删除语句，该语句将删除多余的行，仅留下每个要素的一个副本。

这种类型的问题以前已经被问过，我尝试了使用distinct创建表，但是如何获得所有列而不命名它们？这只获取单个列FID，并且列出要保留的所有列会给出“ORA-00936：缺少表达式”错误。

CREATE TABLE secondtable NOLOGGING as select distinct FID from TABLE

- GIS-Jonathan

---

在你得到解决方案之后，我强烈建议你对数据库进行规范化处理，因为重复记录（所谓的冗余）很可能是未经规范化的数据库的结果。 - Kushal

@Kush - 我们无法对数据库进行规范化，因为许多不同的应用程序都在使用它。这是由于某人在2007年将数据加载两次造成的结果，但我们并没有经常使用它，所以之前没有注意到。感谢您的建议。 - GIS-Jonathan

Answer 1

如果您不关心保留哪一行。

DELETE FROM your_table_name a
 WHERE EXISTS( SELECT 1
                 FROM your_table_name b
                WHERE a.fid = b.fid
                  AND a.unique_id < b.unique_id )

完成此操作后，您需要向表中添加一个约束条件，以确保FID是唯一的。

- Justin Cave

---

如果两个记录中的所有值都相同，则此方法将无法正常工作。 - Zesty

@Zesty - 非常正确。但问题特别提到了有一个unique_id列是唯一的。 - Justin Cave

Answer 2

试试这个

DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.FID = B.FID)

- bitoshi.n

---

理论上来说，这与Justin Cave的方法相同，但是你的方法没有起作用，因为它给了我一个数据类型错误（尽管我的rowid是一个整数）。 - GIS-Jonathan

Answer 3

你可以试试这个。

delete from tablename a
where a.logid, a.pointid, a.routeid) in (select logid, pointid, routeid from tablename 
group by logid, pointid, routeid having count(*) > 1)
and rowid not in (select min(rowid) from tablename
group by logid, pointid, routeid having count(*) > 1)

- GongchuangSu

---

在编写代码时，包含一个简短的解释来说明你的代码是做什么的被认为是良好的礼仪。 - Neil

Answer 4

一个建议。

DELETE FROM x WHERE ROWID IN
(WITH y AS (SELECT xCOL, MIN(ROWID) FROM x GROUP BY xCOL HAVING COUNT(xCOL) > 1)
SELCT a.ROWID FROM x, y WHERE x.XCOL=y.XCOL and x.ROWIDy.ROWID)

- hogni89

Answer 5

试试这个。

DELETE FROM firsttable WHERE unique_ID NOT IN 
(SELECT MAX(unique_ID) FROM firsttable GROUP BY FID)

编辑： 一个解释：

SELECT MAX(unique_ID) FROM firsttable GROUP BY FID;

这个 SQL 语句将从每个重复行组中选择每个最大的 unique_ID 行。删除语句将保留这些最大的 unique_ID 行，并删除每个重复组的其他行。

- Shahriar Hasan Sayeed