Re: SQL statement to find and delete double entries

[Ian Harisay]

How about if you put a proper constraint on the table.  Then it really 
doesn't matter how elegant you are in cleaning up your data, since the 
it should only happen once.

-Ian

Christian Merz wrote:

Hello,

i am definitely sure that the statement below would mess up your data. The
rowid is an internal (physical) access path to your data and it is fatal to
use it as a logical sorting criteria.
To delete ALL duplicates: see my suggestion below.
To leave the first element in your table, you will at first have to define,
which one actually IS the first duplicate in your table.
For example you may only consider your key field 'id':
REM get/check the 'first' of your duplicates (key-rowid-pairs):
select
id, min(rowid), count(*)
from table
group by id
having count(*) > 1;
REM delete duplikates, ignoring the 'first'
REM rememer: i did not actually check this code; but i think it is OK; youn
may let me know...
delete from table
where id in ( select id from table group by id having count(*) > 1 )
and (id, rowid) <> ( select id, min(rowid) from table group by id having
count(*) > 1 );
On the other hand you my define the 'first' as a pair of two (or more)
fields. I did not check this...
But in every case you will loose the information stored in the extra fields
of your table. I would rather suggest to seriously review your data model
...
cu, Christian

----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "Tim Bunce" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, September 22, 2003 6:36 PM
Subject: Re: SQL statement to find and delete double entries
 

Thanks, Tim.  Adding Oracle to your search yielded the following quickly.

delete from T t1
where t1.rowid >
            ( select min(t2.rowID) from T t2
              where t1.col1 = t2.col1
              and t1.col2 = t2.col2);
I ought to know better and just go googly early.



____________________________
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
____________________________




Tim Bunce <[EMAIL PROTECTED]>
09/19/2003 03:09 PM
       To:     Jeffrey Seger/Corporate/[EMAIL PROTECTED]
       cc:     Christian Merz <[EMAIL PROTECTED]>,
   

[EMAIL PROTECTED],
 

"Morrison, Trevor (Trevor)" <[EMAIL PROTECTED]>
       Subject:        Re: SQL statement to find and delete double
   

entries
 

It's a common problem. You can start here:

                http://www.google.com/search?as_q=sql+delete+duplicate

and add the name of the database your using.

Tim.

On Fri, Sep 19, 2003 at 01:31:20PM -0400, [EMAIL PROTECTED]
wrote:
   

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