The only problem with that approach is that it deletes all of the entries
and doesn't leave "singles" behind. I'd probably do it programatically.
Grab the results of query 1, store the data in a hash of hashes, then do
the delete and re-insert.
But I'd love to hear an SQL solution to leaving one copy of each duplicate
behind.
____________________________
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
____________________________
"Christian Merz" <[EMAIL PROTECTED]>
09/18/2003 08:33 AM
To: "Morrison, Trevor (Trevor)" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
cc:
Subject: Re: SQL statement to find and delete double entries
Hi,
the basic idea to find duplicate or multiple values is:
select id, count(*)
from table
group by id
having count(*) > 1;
to delete ALL such values you may do this:
delete from table where id in
( select id
from table
group by id
having count(*) > 1
);
cu, Christian
----- Original Message -----
From: "Morrison, Trevor (Trevor)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, August 16, 2003 6:39 PM
Subject: SQL statement to find and delete double entries
Hi,
What would be an SQL statement that will find duplicate order numbers in
table and then delete them?
TIA
Trevor
