Delete duplicate rows based on a fields value

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 2005 Forums

Transact-SQL (2005)

Delete duplicate rows based on a fields value

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-07 : 11:33:25

For some reason due to a bug on my frontend app, it created many duplicate rows, now want to delete those rows based on englishdescription (field), if more than 1 row with same englishdescription exists then keep one row and delete the rest.

delete TAB_Netorders where orderkey='lblsubject' and checking englishdescription if more than 1 row delete them.

Thank you very much for the helpful information.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-07 : 11:54:08

As long as you have a ID column for the row you can do this


select Row_Number() over (Partition by a.EnglishDescription Order by a.MyID) as RowNo,a.MyID
into #Tmp
TAB_Netorders a

delete a
from
Tab_NetOrders a
inner Join
#Tmp b
on a.MyID = b.MyID
where b.RowNo > 1

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-07 : 12:30:21

Hello Vinnie881,
I used the following based on your exapmple: getting error incorrect syntax a:
**********************

select Row_Number() over (Partition by a.EnglishDescription Order by a.TextID) as RowNo,a.TextID
into #Tmp
TAB_ccsNetPickLists a where a.fieldlabelkey='lblsubject' ---- here on this line
delete a
from
TAB_ccsNetPickLists a
inner Join
#Tmp b
on a.TextID = b.TextID
where b.RowNo > 1

Thank you very much.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-07 : 12:31:39

my bad..

select Row_Number() over (Partition by a.EnglishDescription Order by a.TextID) as RowNo,a.TextID
into #Tmp
from
TAB_ccsNetPickLists a
where a.fieldlabelkey='lblsubject' ---- here on this line

delete a
from
TAB_ccsNetPickLists a
inner Join
#Tmp b
on a.TextID = b.TextID
where b.RowNo > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources