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.
| Author |
Topic |
|
JJins
Yak Posting Veteran
81 Posts |
 Posted - 2010-12-14 : 11:13:28
|
| Does any one have helpful code to delete duplicate records that have unique primary codes? my tables have multiples rows where everything is the same except the primary key number so that when it is selected it is double the amount I want.Thanks for the HELP!Best,GG |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-12-14 : 11:26:58
|
Sql Server version and example data would be great.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
JJins
Yak Posting Veteran
81 Posts |
Posted - 2010-12-14 : 11:31:38
|
| SQL Server Management Studio 2005, I have table 1 with collums A,B,C,D,ECollums A are all different. but if B,C,D and E are the same for more then one row I would like to delete that row or rows. Does that help?Thank you.best,Graham |
|
|
|
JJins
Yak Posting Veteran
81 Posts |
Posted - 2010-12-14 : 11:33:04
|
| Note: I just want one row of unique data in the database. |
|
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-12-14 : 11:50:55
|
| Have a look at:http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx |
|
|
|
JJins
Yak Posting Veteran
81 Posts |
Posted - 2010-12-14 : 12:05:47
|
| Can you apply that to my example. I read your blog and I am nervouse running a statement that has a partition in it. I have never used that. Thanks you.Best,GG |
|
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-12-14 : 12:21:10
|
| Post some sample data and expected output. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-12-14 : 13:02:47
|
You should try some practice select queries from the link above to get familar with partition functions.Here is a query that will do what you want. Simply remove the SELECT * and uncomment the DELETE to perform the actual delete:SELECT *-- DELETE AFROM(SELECT * ,ROW_NUMBER() OVER(PARTITION BY B, C, D ORDER BY A) AS RowNumFROM MyTable ) AS A WHERE RowNum >= 2 |
|
|
|
|
|
|
|
|
Delete Duplicate Records