I am using postgres. I want to delete Duplicate rows. The condition is that , 1 copy from the set of duplicate rows would not be deleted.
i.e : if there are 5 duplicate records then 4 of them will be deleted.
-
1possible duplicate of How to delete duplicate rows with SQL?Georg Schölly– Georg Schölly2010-09-23 11:08:40 +00:00Commented Sep 23, 2010 at 11:08
-
1how ironic! lol 'possible duplicate of how to delete duplicates'...Denis Valeev– Denis Valeev2012-02-24 21:18:20 +00:00Commented Feb 24, 2012 at 21:18
Add a comment
|
5 Answers
Try the steps described in this article: Removing duplicates from a PostgreSQL database.
It describes a situation when you have to deal with huge amount of data which isn't possible to group by.
A simple solution would be this:
DELETE FROM foo
WHERE id NOT IN (SELECT min(id) --or max(id)
FROM foo
GROUP BY hash)
Where hash is something that gets duplicated.
4 Comments
pomarc
doesn't this delete all the rows that don't have duplicates, too?
Denis Valeev
@pomarc no, because there's this little equals sign (=) before 1 that tells us that we want to take min(id) of all possible groups even those that contain only one member; so, no worries, you won't delete data that is not duplicated
grteibo
is the having count (*) >=1 neccesary ? i got the same result if I execute : DELETE FROM foo WHERE id NOT IN (SELECT min(id) FROM foo GROUP BY hash)
Denis Valeev
@grteibo you are absolutely right, that's the way the deduplication is usually done; I don't know why I didn't notice that before; the idea of this answer is not so much the idea of deduplication itself but the fact that we calculate a hash for all the columns that we want to group by and then remove duplicates
delete from table
where not id in
(select max(id) from table group by [duplicate row])
This is random (max Value) choice which row you need to keep. If you have aggre whit this please provide more details
Comments
The fastest is is join to the same table. http://www.postgresql.org/docs/8.1/interactive/sql-delete.html
CREATE TABLE test(id INT,id2 INT);
CREATE TABLE
mapy=# INSERT INTO test VALUES(1,2);
INSERT 0 1
mapy=# INSERT INTO test VALUES(1,3);
INSERT 0 1
mapy=# INSERT INTO test VALUES(1,4);
INSERT 0 1
DELETE FROM test t1 USING test t2 WHERE t1.id=t2.id AND t1.id2<t2.id2;
DELETE 2
mapy=# SELECT * FROM test;
id | id2
----+-----
1 | 4
(1 row)
Comments
delete from table t1
where rowid > (SELECT min(rowid) FROM table t2 group by
t2.id,t2.name );
Comments
DELETE f1 from foo as f1, foo as f2
where f1.duplicate_column= f2.duplicate_column
AND f1.id > f2.id;