Duplicate records

[Sophia Rekhi]

Hi all, How to delete duplicate Records in a table in SQLSERVER.

[jijoaresseriljose]

hi, suppose col001 of table1 is the duplicated values.then the query "select col001 from table1 group by col001 having count(col001)>1" will get all the duplicate values so, the below query will delete all the duplicated records. "delete from table1 where col001 in ( select col001 from table1 group by col001 having count(col001)>1)" regards Jijo

jijo jose

[Krish KP]

Add new column to the table and make it IDENTITY so that each row can be identified Uniquely.

Regards KP

[Sophia Rekhi]

Hi jijo jose, Thanks for your response, but your solution deletes all records which are duplicate.even table does n't contain one value. i want only delete duplicated records other than first value' ex: Sample Table Contains records are sno sname 10 XXX 10 XXX 10 XXX 20 YYY 20 YYY But after deletion of duplicate records i want records in table like this : sno sname 10 XXX 20 YYY

[one_stone_zz]

You can use the temp table, put the only exist records into the temp table then you can delete the original table's records use the condition with the information of the temp table. onestone

[Krish KP]

in continuation to my earlier solution.... select * from tblA where newcol IN (select min(newcol) from tblA group by sno having count(*) > 1) should give first occurance of the each duplicate row. Note : newcol is the new identity colum

Regards KP

[jijoaresseriljose]

hi, I thought, you wanted to delete all the dubplicated one including original, Then do this in your query analyser......... 1 st step ---------- alter table table1 add [id] int identity 2nd step ---------- delete from table1 where [id] not in (select min([id]) from table1 group by col001) it will delete all the duplicate records from table1 regards Jijo :rolleyes: -- modified at 9:36 Thursday 29th March, 2007

jijo jose