Delete duplicate entry - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Delete duplicate entry

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2011-12-21 : 04:02:57

Dears,
I have table named: Customers. This table have two fileds Account (nvarchar) & email (nvarchar). Here my question is i want to select duplicate items where account & email are same. so based on that I wrote this query and its selecting fine.
SELECT ACCOUNT, email, count(ACCOUNT) FROM Customers GROUP BY email, ACCOUNT HAVING count(email) > 1

Now I need to delete these duplicated items but need to keep one item in the table.

Thanks dears.

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-21 : 04:39:22

[code]
delete d
from
(
select *, row_no = row_number() over (partition by email, ACCOUNT order by email)
from Customers
) d
where d.row_no <> 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]