Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Optimised: deleting Duplicate record from table.

Author  Topic  sinhakg Starting Member 1 Post Posted - 2009-08-18 : 11:54:17 Hi, I saw many solution for deleting duplicate rows in many articles published on various website. Even in the forum of msdn [url]http://support.microsoft.com/kb/139444 [/url] Every solution anyhow delete the existing table in which the duplicate rows are found.(Either copy or murge the table to new one. Now I will provide one better solution to delete the table's duplicate row without deleting table. Simplest way to delete uplicate records from table having Primary Key Let a table tblA with column X, Y and Z has some duplicate rows viz X Y Z 1 abc def 2 abc fgh 3 cdf fgh 4 cdf fgh Here I consider X is as Primary Key. Now below is the solution for deleting the duplicate rows possessed by column Y. delete from tblA where X In (select a.X from tblA a inner join tblA b on a.X<> b.X and a.Y= b.Y) and X not in (select min(a.X) from tblA a inner join tblA b on a.X<> b.X and a.Y= b.Y group by a.Y) This is the simplest & optimised solution to delete duplicate records from table having primary key. and finally you will get the result like below: X Y Z 1 abc def 3 cdf fgh Simplest way to delete duplicate records from table having No Primary key e.g. below table (tblA) Y Z abc def abc fgh cdf fgh cdf fgh -> First create a tempoaraty table with all column with respective datatype (@tblTempA) -> select the column for which you have to delete the records (e.g. I choose Y) insert @tblTempA select distinct(Y),Z from tblA delete tblA insert tblA select * from @tblTempA and finally you will get the result like below: Y Z abc def cdf fgh Write me for help at My Mail Always be optimised