Delete duplicate records
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 start.xls Description: application/msexcel result.xls Description: application/msexcel
RE: Delete duplicate records
Hi Rolland, Here the script for check the the duplicate row as per your email. select ean from table_name where varutyp=3 group y ean having count(ean) 1; For deleting the duplicate rows : delete table_name where ean in (select ean from table_name group by ean having count(ean)1) and varutyp =3; regards, Bernardus Deddy Hoeydiono. -Original Message- [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 3:24 PM 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: Bernardus Deddy Hoeydiono INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Delete duplicate records
Ave ! How about something like: delete from varukorgtmp where varutyp = 3 and EAN = (select EAN from varukorgtmp a where rowid != (select min(rowid) from varukorgtmp b where a.EAN = b.EAN)); Should work, but not tested :) Br.Jorma - Name: Jorma Vuorio Phone: +358-9-7180 67759 Company: Nokia Business Infrastucture Fax:+358-9-7180 67465 Address: P.O.Box 321, FIN-00045 NOKIA GROUP, FINLAND Internet: [EMAIL PROTECTED]Mobile: +358-50-486 8043 - -Original Message- Sent: 08 January, 2003 10: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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Delete duplicate records
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
How to delete duplicate records with condition
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 start.xls Description: application/msexcel result.xls Description: application/msexcel
Re: How to delete duplicate records with condition
Hi, following metalink documents may help you. Note:65080.1 Using SQL To Delete Duplicate Rows In A Table PR:1015631.6 HOW TO SELECT DUPLICATE ROWS WITHOUT USING ROWID PR:1004425.6 HOW TO FIND OR DELETE DUPLICATE ROWS IN TABLE Murat roland.skoldblom@ ica.se To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: How to delete duplicate records with condition 01/07/2003 03:28 PM Please respond to 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 (See attached file: start.xls)(See attached file: result.xls) start.xls Description: application/msexcel result.xls Description: application/msexcel
Delete duplicate records with condition
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 start.xls Description: application/msexcel result.xls Description: application/msexcel
