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 |
|
Steve2106
Posting Yak Master
183 Posts |
 Posted - 2012-07-25 : 12:17:35
|
| Hi There,Can someone help me with the sql code to delete duplicate rows.I have a field called UID that is not keyed but there should not be duplicates. I have just merged an old database to a new one and accidentaly inserted duplicate records.I need to delete any rows that have a duplicate based on the field UID.Thanks for your help.Best Regards,Steve |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-25 : 12:21:36
|
| whats rule for determining which one of records with same value for UID to be retained? or is it that they're all perfectly identical and you just need to retain a random one?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Steve2106
Posting Yak Master
183 Posts |
Posted - 2012-07-25 : 14:00:35
|
| Hi There,Thanks for the reply.The records are identical except the primary key autoid.Best Regards,Steve |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-25 : 14:05:59
|
quote:
Originally posted by Steve2106
Hi There,Thanks for the reply.The records are identical except the primary key autoid.Best Regards,Steve
so which ones to be retained?one with earliest,latest or random value of pk?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Steve2106
Posting Yak Master
183 Posts |
Posted - 2012-07-31 : 06:11:09
|
| Hi There,Thanks for the reply.I'm sorry it has taken a while to answer your question. I have been off ill.Preferably I would like the earlier ones to stay.Thanks for your help, as always I appreciate it.Best Regards,Steve |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-07-31 : 06:50:48
|
| If you can determine how to keep one and not another they're not identical are they?So first you need to work out how identical they really are:select col1, col2, coln .. all except GUID from tablegroup by col1, col2, coln having count(*)>1Then you'll know what you're up against.Then if you can just delete any old one you need to select * from(select col1, col2, col3, guid, rownum() over (order by ... partition by col1, col2...) rn) xwhere rn=1and delete those.Something like that - There's plenty of examples on this forum if you search a little harder.Anyway, the reason for posting this:Remember when chosing a surrogate key, *you also need to create a unique constraint on your actual key*.Now you've done that and counted the cost in maintaining the same rule twice, think if you really need that surrogate after all. |
|
|
|
|
|
|
|
|
Duplicates