从小表中删除重复的行 [英] Delete duplicate rows from small table

问题描述

我在PostgreSQL 8.3.8数据库中有一个表，该表上没有键/约束，并且有多行具有完全相同的值。

I have a table in a PostgreSQL 8.3.8 database, which has no keys/constraints on it, and has multiple rows with exactly the same values.

我要删除所有重复项，每行仅保留一份副本。

I would like to remove all duplicates and keep only 1 copy of each row.

特别是有一列（称为键）可用于标识重复项，即应仅存在一个条目每个不同的键。

There is one column in particular (named "key") which may be used to identify duplicates, i.e. there should only exist one entry for each distinct "key".

我该怎么做？ （理想情况下，使用一个SQL命令。）

在这种情况下，速度不是问题（只有几行）。

How can I do this? (Ideally, with a single SQL command.)
Speed is not a problem in this case (there are only a few rows).

推荐答案

DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

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