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 |
|
ravl13
Starting Member
38 Posts |
 Posted - 2012-06-20 : 16:22:05
|
| Greetings,I have run the following query on a table called SubscribedEmailsBNB: SELECT semail FROM SubscribedEmailsBNB GROUP BY semail HAVING (COUNT(semail)>1)This query returns all emails that occur in more than one row. semail is currently the only column in the table, so there are no primary keys. My end goal is to run the above query and get zero results. Could you someone give me guidance on how I can delete the "duplicate rows"?EDIT: I wound up giving the table a primary key, so I could use the duplicate removal method shown at: http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/However, is there a more elegant way to remove the duplicates from a one column table? (Without creating a primary key) |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-06-20 : 17:03:23
|
delete dt from(select row_number() over (partition by semail order by semail) as rn,* from SubscribedEmailsBNB)dtwhere rn > 1
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-06-20 : 18:40:41
|
| 1) Design your database and application properly (PK, unique constraint etc).2) select distinct semail into new_table from SubscribedEmailsBNB 3) drop table semail 4) sp_rename semail new_table |
|
|
|
ravl13
Starting Member
38 Posts |
Posted - 2012-06-21 : 09:41:36
|
quote:
Originally posted by webfred
delete dt from(select row_number() over (partition by semail order by semail) as rn,* from SubscribedEmailsBNB)dtwhere rn > 1
No, you're never too old to Yak'n'Roll if you're too young to die.
OK, yeah that works, thanks.This table I'm using is not a permanent table I will be using. I was just curious if it was possible to clear the column of duplicates without using PKs or creating a new table. There's a lot of things in the solution from LoztinSpace that I have not seen before though, so it's probably safer to do things the "proper" way with PKs and creation of temporary tables, even though it takes a couple extra steps.Thanks all. |
|
|
|
|
|
|
|
|
Removing Duplicate Rows