从小表中删除重复行 [英] 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.

我想删除所有重复项并只保留每行的 1 个副本.

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

有一个特定的列(名为key")可用于识别重复项，即每个不同的key"应该只存在一个条目.

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屋！