How to delete the duplicate records in a table

sundar_svb · New User Joined: 16 Oct 2005 Posts: 1

hi ..
how to delete duplicate records in a table.....

By,
Sundar

sairam · New User Joined: 30 Aug 2005 Posts: 58 Location: Kolkata

Hi Sundar

I think it is not possible to delete duplicate records keeping one from the table with no primary key.
So to add an primary key (ex. id) then follow this query

Delete from Test s where id <
(Select Max(id) from Test t where s.a = t.a and
s.b = t.b and
s.c = t.c)

or you can transfer the one distinct record to another table and delete all in original table and then transfer the distinct one back.

Try this one (i am not sure)
for transferring distinct record to a table which is not exist

select distinct * into <NewTable> from <MyTable>

paulstephen · New User Joined: 20 Oct 2005 Posts: 20 Location: Chennai

If its a one time requirment u can also use file aid for DB2.
If its on a routine basis plz use a cursor with for delete of option in ur application program