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 |
|
johnvm
Starting Member
18 Posts |
 Posted - 2007-08-31 : 15:32:35
|
| Hey guys, I've been reading up on other threads about how to purge duplicate rows, etc., but my situation is a little different from what I've found so far so I figured I'd make a new post.I'm trying to set a field to be the master key of a table, but there are duplicate entries. Select DISTINCT doesnt work, because for instance, these might be two rows (fields separated by commas):KEYCOLUMN,COLUMN2,COLUMN3key1, abcde, abcdekey1, abcdef, abcdefSo the two rows are distinct from eachother, but the key column in each is not. I need a way to delete all the rows where the key column is not distinct.... so then i can set that key column as the primary key. How can I go about doing this? Thanks! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-31 : 16:34:31
|
| How do you know which row to keep? |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-31 : 16:34:59
|
| do a group by on the key column and query for count(*) > 1. this will give you all dup records.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
johnvm
Starting Member
18 Posts |
Posted - 2007-09-01 : 02:00:21
|
| i don't care which record i keep. this isn't critical data, i just need the duplicates gone. to be honest i could even delete both records in an instance of a duplicate and be happy.will fiddle to see if i can get that dupe by method working -- i'm a newb at sql though so might have limited success. examples would be appreciated.thx for the help given already and any more coming! |
|
|
|
johnvm
Starting Member
18 Posts |
Posted - 2007-09-01 : 02:12:24
|
| SELECT id, count(*)FROM tableGROUP BY idHAVING count(*) > 1seems to work. thanks guys. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-01 : 04:06:55
|
DELETE fFROM (SELECT Col1, ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 ORDER BY Col1) AS RecID FROM Table1) AS fWHERE RecID > 1
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|
|
How can I purge rows that match a duplicate field?