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
>
>
>
>
>
