删除重复的记录,保留一个
我有一个从CSV文件的copy
创建的临时表,结果包括一些重复的ID。我需要删除任何重复。我试过以下内容:删除重复的记录,保留一个
delete from my_table where id in
(select id from (select count(*) as count, id
from my_table group by id) as counts where count>1);
但是,这会删除重复的记录,我必须保留一个。
如何删除带有重复ID的第2条记录?
谢谢。
您的查询将删除计数大于1的所有ID,因此它会删除所有重复的内容。你需要做的是从副本列表中隔离一个记录和保存认为:
delete
from my_table
where id in (select id
from my_table
where some_field in (select some_field
from my_table
group by some_field
having count(id) > 1))
and id not in (select min(id)
from my_table
where some_field in (select some_field
from my_table
group by some_field
having count(id) > 1)
group by some_field);
编辑修正:P
min(id)... group by id? –
@Irit:点了!固定:)我的例子是多列数据;如果只有一个列(ID),那么OP应该在ID列上放置一个PK约束并再次运行导入! – Xophmeister
Dammit:犯了另一个错误...好的:它现在已经修复,它的工作:) – Xophmeister
假设你没有外键关系......
CREATE TABLE "temp"(*column definitions*);
insert into "temp" (*column definitions*)
select *column definitions*
from (
select *,row_number() over(PARTITION BY id) as rn from "yourtable"
) tm
where rn=1;
drop table "yourtable";
alter table "temp" rename to "yourtable";
这个临时表只是一个列吗? DISTINCT将保留他们当然.... –
对不起,不,它是5列。 – pstanton