This post is related to this tip: Deleting duplicate rows when there is no primary key on a SQL Server database table

http://www.mssqltips.com/tip.asp?tip=1103

The corelated query in TOP clause is not working .. Basharat Bhat has given only for single dup entry

Bals,

Can you please post the code you are having an issue with?

Thank you,
The MSSQLTips.com Team

There is another very good way to do this, in SQL Server 2005, if you do not have the key set up, but want to effectively use a column or more as your primary key.

 ;with DelDup as (select row_number() over (partition by
FirstName, LastName order by FirstName, LastName) as RowNofrom duplicateTest)
Delete from DelDup where RowNo> 1

--alternate--

 ;with DelDup as (select row_number() over (partition by
ID order by ID) as RowNofrom duplicateTest)
Delete from DelDup where RowNo> 1

You can easily adjust this to use one column or as many as you wish, as long as you update the column(s) after the partition by and after the order by.

Thank you both for the feedback.

Thank you,
The MSSQLTips.com Team

Hi Friend Pls test the following one, dis s a very simple method

Delete Top (Select Count(*)-1 From EmpTest Where EmpId=A.EmpId)
From EmpTest As A

 

 

It didnt work Prakash,

gave me the follwoing error

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'As'."