SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to delete row number on where statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Thechewinggummonster
Starting Member

United Kingdom
8 Posts

Posted - 11/07/2013 :  13:28:34  Show Profile  Reply with Quote
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

USA
35954 Posts

Posted - 11/07/2013 :  13:36:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
Search the forums and google for "deleting duplicates". So many solutions.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/08/2013 :  01:18:42  Show Profile  Reply with Quote
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


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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000