RE: Delete duplicate records

From: Nicoll, Iain <IAIN.D.NICOLL_at_saic.com>
Date: Wed, 08 Jan 2003 02:27:08 -0800
Message-ID: <F001.00529AB0.20030108022708@fatcity.com>

Roland,

select *
from varukorgtmp v
where exists (select ''

              from varukorgtmp v2
              where v2.ean = v.ean
              and   v2.rowid != v.rowid)

will show duplicates of ean

select *
from varukorgtmp v
where exists (select ''

              from varukorgtmp v2
              where v2.ean = v.ean
              and   v2.rowid != v.rowid)

and varutyp = 3

will show duplicates of ean with varutyp of 3

delete varukorgtmp v
where exists (select ''

              from varukorgtmp v2
              where v2.ean = v.ean
              and   v2.rowid != v.rowid)

and varutyp = 3

As always check the results before committing (especially as I quite often get the exact syntax wrong). Also if the tables are big then this may not be the most efficient way to do it.

Iain Nicoll

-----Original Message-----
Sent: 08 January 2003 08:24
To: Multiple recipients of list ORACLE-L

Hallo,

I have this sql,

SELECT * FROM varukorgtmp
where varukorgid= 120 That makes the result of this testfile.xls

(See attached file: start.xls)

There are two values in EAN-field, which are the same 23324614 in row 2 and 3

Now I want in an sql script to check out which are the duplicates of EAN in that table. Then then the script will check which is VARUTYP = 3, then delete the record(s) which have VARUTYP =3.

So the result should be like this, with only two rows left in this case).

(See attached file: result.xls)

I would really appreciate if anyone could help me with this sql I have tried several sql, but with no luck.

Thanks in advance

Roland

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nicoll, Iain
  INET: IAIN.D.NICOLL_at_saic.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Wed Jan 08 2003 - 04:27:08 CST