How to delete duplicate records in a table

[Pamy]

Hi, I would like to run an SQL query to delete all duplicate records in a table. Problem is I can't think of an easy way to do it. Can anyone please enlighten me? Thank you very much!

 

[r937]

delete all duplicates, or delete all but one?

what are the duplicate criteria? obviously the table does not have a primary key, so what are the columns involved?

rudy

 

[Pamy]

There is no primary key in this table.
Eg
Serial#, Name, Option1, Option2
================================
01 Peter 12 23
02 Susan 12 35
01 Peter 12 23
03 Peter 12 24

The system should check for duplication in Serial# column, and delete all duplicates except one. In this case, the system should keep only

02 Susan 12 35
01 Peter 12 23
03 Peter 12 24

Thanks!

 

[r937]

whoa, that's different

by saying to check only the serial number, it means you don't care what's in the other columns

you can do it like this --

select Serial#
     , min(Name)    as c1
     , min(Option1) as c2
     , min(Option2) as c3
  into temptable
  from yourtable
group
    by Serial#

delete from yourtable

insert into yourtable
 (Serial#, Name, Option1, Option2)
select Serial#, c1, c2, c3
  from temptable

drop temptable