How to delete row number on where statement

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

How to delete row number on where statement

Author

Topic

Thechewinggummonster
Starting Member

8 Posts

Posted - 2013-11-07 : 13:28:34

I have duplicate rows of data with the exact same data, for multiple sets of data.

For example,

1 Fred Flintstone
1 Fred Flintstone
2 Barney Rubble
2 Barney Rubble
etc...

If I use the delete and where command, it will delete both rows and I want to keep one of them. I don't want to copy down each one and re insert as there are too many columns for each one and there are too many rows also.

I was thinking of a delete row-number command in a where statement. but tried multiple forums and its not working. I am not sure which sql I am using it, but I think it is the latest one.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-07 : 13:36:16

Search the forums and google for "deleting duplicates". So many solutions.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 01:18:42

rownumber solution would look like this


DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID,Name ORDER BY ID) AS Seq
FROM Table
)t
WHERE Seq>1

First make it into SELECT as below and check whether it gives you correct duplicates and once happy use the above


[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID,Name ORDER BY ID) AS Seq,*
FROM Table
)t
WHERE Seq>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources