Thursday, March 13, 2008

How to delete the rows which are duplicate (don’t delete both duplicate records).

SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0

1 comment:

Narasimha said...

This link discribes different methods of deleting duplicate rows

http://www.besttechtools.com/SQLArt
icles.aspx?ID=DeleteDuplicate