Duplicate Values

[VBXL]

What is the quickest way to delete duplicate values in a table in a SQL Statement. Please you you give an example.


i.e I want to delete all duplicate values.
Bob
Bob
Fred
John
Tom

I would not know what would duplicate.

Cheers

 

[JayKusch]

Here is a quickie way i have done something similiar:

add a bit column to your table, like dupe_hold.

run a script that set the dupe_hold field to 1 based on the duplicates that you find.

Update TableName
Set dupe_field = 1
from TableName
where
(select count(UserName), UserName
from TableName
group by UserName
having count(UserName) > 1)


the run a script that grabs only one instance of the duped records and set that field to 0, zero.

Update TableName
Set dupe_field = 0
from TableName
where
(Select top 1
from TableName
where dupe_field = 1)

now delete the remaining records that have a 1 in the dupe_field column.

delete
from TableName
where dupe_field = 1

drop the field from the table and now your dupes are gone.

 

[JayKusch]

Sorry ...

started you off w/ the field named dupe_hold and then used dupe_field in all the scripts. need to add the bit field in the table called "dupe_field" so that it matches the scripts i sent you.