On 9/5/07, Håkan Jacobsson <hakan.jacobsson99@xxxxxxxxxxxx> wrote:
> Hi,
>
> I want to create a DELETE statement which deletes duplicates
> in a table.
>
> That is, I want to remove all rows - but one - having three
> columns with the same data (more columns exist and there the
> data varies).
Assuming you've got a KNOWN unique id field (adding one if you don't)
you can do something like:
select * from table t1 join table t2 on (t1.field1=t2.field1 AND
t1.field2=t2.field2 AND t1.field3=t2.field3 AND t1.uid>t2.uid)
That should get the ids of all but one of the matching rows. then
just use that in a subselect:
begin;
delete from table where uid in (select * from table t1 join table t2
on (t1.field1=t2.field1 AND t1.field2=t2.field2 AND
t1.field3=t2.field3 AND t1.uid>t2.uid) );
(check for dups / lost data)
commit;
or something like that.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
