Re: better delete statement to remove duplicate rows from exception
Hi, You can do this: delete from orders o1 where rowid < (select max(rowid) from orders o2 where o1.order_id = o2.order_id group by order_id having count(order_id) > 1); Obviously you cand firstly do a select statment (on a limited set of data), instead of a delete statement, just to be sure it works as expected. Best regards Dias Costa Jacques Kilchoer wrote: In the situation below, is there a better way to write the delete statement that eliminates duplicates? (assuming duplicate rows form at most 5 % of the table rows) Notice that the exceptions table is not analyzed. If I analyze the exceptions table, is there then another better way to write it? create table my_exceptions (row_id urowid, owner varchar2 (30), table_name varchar2 (30), constraint varchar2 (30) ); create table orders (order_id number (8) not null, order_date date, constraint orders_uq1 unique (order_id) disable ) ; /* -- load table orders with millions of rows */ create index orders_idx1 on orders (order_id) ; analyze table orders estimate statistics sample 10 percent ; alter table orders enable constraint orders_uq1 exceptions into my_exceptions ; delete from orders a where a.rowid in (select d.delete_row_id from (select min (b.row_id) over (partition by c.order_id) as keep_row_id, b.row_id as delete_row_id from my_exceptions b, orders c where c.rowid = b.row_id group by c.order_id, b.row_id ) d where d.delete_row_id != d.keep_row_id ) ; commit ; alter table orders enable constraint orders_uq1 ; truncate table my_exceptions ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dias Costa 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).
better delete statement to remove duplicate rows from exception table?
In the situation below, is there a better way to write the delete statement that eliminates duplicates? (assuming duplicate rows form at most 5 % of the table rows) Notice that the exceptions table is not analyzed. If I analyze the exceptions table, is there then another better way to write it? create table my_exceptions (row_id urowid, owner varchar2 (30), table_name varchar2 (30), constraint varchar2 (30) ); create table orders (order_id number (8) not null, order_date date, constraint orders_uq1 unique (order_id) disable ) ; /* -- load table orders with millions of rows */ create index orders_idx1 on orders (order_id) ; analyze table orders estimate statistics sample 10 percent ; alter table orders enable constraint orders_uq1 exceptions into my_exceptions ; delete from orders a where a.rowid in (select d.delete_row_id from (select min (b.row_id) over (partition by c.order_id) as keep_row_id, b.row_id as delete_row_id from my_exceptions b, orders c where c.rowid = b.row_id group by c.order_id, b.row_id ) d where d.delete_row_id != d.keep_row_id ) ; commit ; alter table orders enable constraint orders_uq1 ; truncate table my_exceptions ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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).
bcv copy -> duplicate rows unique prim key
Hi, When I issue an alter table move statement and afterwards an alter index rebuild for the primary key on this table I got an error duplicate rows found. This primary key is unique. The database has just been migrated for test purposes. In production we are still on oracle 7.3.4. I found the offending rows by validating into an exceptions table. When I compared these with production it didn't exist there. I turned out that these records were updated during the time of copying the database. This database is on EMC Symmetrix and the copy has been made through a bcv. Before starting the bcv all tablespaces are put into backup mode and then a split is done through EMC software to get a copy. About 20 seconds before the update in the table the tablespace has been put into backup mode and 40 seconds after the update the tablespaces are brought back to normal. Besides a hotbackup to tape we use this bcv regularly to copy databases so this behaviour is starting to worry me. Any idea what might be going wrong here? Regards, Jeroen Details: Production : HP-UX 10.20, Oracle 7.3.4.3, Symmetrix 3330 Test: HP-UX 11.11 , Oracle 9.2.0.4
RE: Removing duplicate rows from a table !
Assuming that we have duplicate rows ( NOT duplicate keys ), you may try this. I have borrowed this from a friend of mine. We have used it successfully. Each of the columns in the table are included as predicates to confirm that we are deleting only the duplicate rows. delete from noderelatedreplicationqueue a where rowid < (select max(rowid) from noderelatedreplicationqueue b where a.NODENBR = b.NODENBR and a.LASTUPDATETS = b.LASTUPDATETS and a.NODEABOVENBR = b.NODEABOVENBR and a.TRANSACTIONTYPEIND = b.TRANSACTIONTYPEIND and a.NODETYPECD = b.NODETYPECD and a.NODEABOVETYPECD = b.NODEABOVETYPECD and a.DISTANCENBR = b.DISTANCENBR and a.MARKETCD = b.MARKETCD and a.TOPLEVELIND = b.TOPLEVELIND and a.LOGICALDELETEIND = b.LOGICALDELETEIND and a.LASTUPDATENBR = b.LASTUPDATENBR ) Hope this helps. Govind -Original Message- Rama, Shreekantha (K.) Sent: Thursday, October 16, 2003 3:45 PM To: Multiple recipients of list ORACLE-L Hi, I am trying to remove the duplicate rows from a table with the column data.. I cannot use PK as it's just a sequence number... I could find all the duplicate rows by grouping the column. but how can i delete only the duplicate ones and retain the original data.. Any help is gr8 ! ! ! Warm Regards Shreekanth -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rama, Shreekantha (K.) 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). -- 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: Removing duplicate rows from a table !
We just discussed this about a month ago. 3 basic solutions: 1. delete from table where rowid not in (select max(rowid) from table group by col_1,col_2,etc); sql only solution, not really feasible in huge environments 2. Alter table mytab enable constraint PK exceptions into exceptions; Better way; much faster for large tables, lets you audit the duplicate rows by examining exceptions table. 3. Write a cursor; sql coding solution ... probably doesn't give you anything mroe than what option 2 provides. boss > > Hi, > > I am trying to remove the duplicate rows from a table with the column data.. > I cannot use PK as it's just a sequence number... > > I could find all the duplicate rows by grouping the column. but how can i > delete only the duplicate ones and retain the original data.. > > Any help is gr8 ! ! ! > > Warm Regards > Shreekanth > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rama, Shreekantha (K.) > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Todd Boss 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).
Removing duplicate rows from a table !
Hi, I am trying to remove the duplicate rows from a table with the column data.. I cannot use PK as it's just a sequence number... I could find all the duplicate rows by grouping the column. but how can i delete only the duplicate ones and retain the original data.. Any help is gr8 ! ! ! Warm Regards Shreekanth -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rama, Shreekantha (K.) 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: PL/SQL Question:Eliminate duplicate rows
Job well done, Tom, the embarrasment is of no consequence. The routine was modified to include Ron's pointer. Stats for Tom's first routine below: load table 1.3 million rows, results table (deduped rows) 48,307. Completion time (via sqlplus over network) 63 seconds. Second routine (same tables) : 21 seconds! (Oracle 8.1.7 on AIX 4.3 IBM M80 (6 way Risc 6000 4 Gig Ram 1.2 TB IBM Shark attached array) --- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote: > You know, I never use that exception, so I can't remember it correctly. > > You are correct, of course - thanks for embarrasing me in front of thousands > and thousands and thousands (how many Jared??) of people! :) > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Friday, September 19, 2003 4:10 PM > To: Multiple recipients of list ORACLE-L > > > > except your too_many_rows exception should be dup_val_on_index... > > Ron Thomas > Hypercom, Inc > [EMAIL PROTECTED] > Each new user of a new system uncovers a new class of bugs. -- Kernighan > > > > > [EMAIL PROTECTED] > > e.ny.us To: > [EMAIL PROTECTED] > > Sent by: cc: > > [EMAIL PROTECTED]Subject: RE: PL/SQL > Question:Eliminate duplicate rows > > .com > > > > > > 09/19/2003 01:54 > > PM > > Please respond to > > ORACLE-L > > > > > > > > > > Johann, > > how about the following. what this does is, using the inner begin/end > block, catches when an insert would fail because of the PK failure and > ignores the error. > > This is very quick and dirty - it will work fine if you are not working with > a huge amount of data. > > declare > > cursor c1 is > select col1, col2 >from some_table; > begin > for c1_rec in c1 loop > begin > insert into new_table(col1, col2) > values (c1_rec.col1, c1_rec.col2); > exception > when too_many_rows then >null; > end; > end loop; > end; > / > > If you are talking about lots and lots of data, you could easily query the > table you are inserting into, testing for the existence of the value you are > attempting to insert. If you find it, skip the insert. Like this: > > declare > rec_count number; > cursor c1 is > select col1, col2 >from some_table; > begin > for c1_rec in c1 loop > begin > select count(*) > into rec_count > from new_table > where col1 = c1_rec.col1; -- this assumes that col1 is the pk! > if rec_count = 0 then >insert into new_table(col1, col2) > values (c1_rec.col1, c1_rec.col2); > end if; > exception > when too_many_rows then >null; > end; > end loop; > end; > / > > Good Luck! > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Friday, September 19, 2003 3:05 PM > To: Multiple recipients of list ORACLE-L > > > Looking for an a sample cursor routine to load a PK enabled table to > eliminate > any dupes from the load table. > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Johan Muller > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mercadante, Thomas F > 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]
RE: PL/SQL Question:Eliminate duplicate rows
Ron, Doesn't matter. On the way to your car, or the gym, or to watch your kids play ball - my Uncle Guido can find you anywhere you go!! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 5:20 PM To: Multiple recipients of list ORACLE-L Hi Tom- Submitting to a listserve is like living in a small town. Make 1 little oops and everybody knows about it. Do I need to look over my shoulder on the way to my car tonight??? ;) Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 02:29 PM Please respond to ORACLE-L You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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
RE: PL/SQL Question:Eliminate duplicate rows
Hi Tom- Submitting to a listserve is like living in a small town. Make 1 little oops and everybody knows about it. Do I need to look over my shoulder on the way to my car tonight??? ;) Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 02:29 PM Please respond to ORACLE-L You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
RE: PL/SQL Question:Eliminate duplicate rows
Check SQL Reference for "exception_clause" when creating Primary Key. Could help to do what you need just using SQL (no PL/SQL). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mercadante, Thomas F Sent: Friday, September 19, 2003 2:55 PM To: Multiple recipients of list ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman 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: PL/SQL Question:Eliminate duplicate rows
You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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 yoursel
RE: PL/SQL Question:Eliminate duplicate rows
except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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 'ListGu
RE: PL/SQL Question:Eliminate duplicate rows
Easy way: delete from table where rowid not in (select max(rowid) from table group by PK); Complicated way: Alter table mytab enable constraint PK exceptions into exceptions; Then, you should see how many rows are duplicated and use the method 1 on that set of rowids. If the table in question is a multi-gigabyte table and the number of rows is relatively small, then the second method is much, much faster. -- Mladen Gogala Oracle DBA > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Johan Muller > Sent: Friday, September 19, 2003 3:05 PM > To: Multiple recipients of list ORACLE-L > Subject: PL/SQL Question:Eliminate duplicate rows > > > Looking for an a sample cursor routine to load a PK enabled > table to eliminate any dupes from the load table. > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design > software http://sitebuilder.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Johan Muller > 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). > Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: PL/SQL Question:Eliminate duplicate rows
Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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).
PL/SQL Question:Eliminate duplicate rows
Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller 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: Duplicate rows
um yeah, that works too :) --- orantdba <[EMAIL PROTECTED]> wrote: > Hi, > > Probably the best way is to attempt to enable a unique constraint and > > use the exceptions table to find the rowid's all rows that violate > the > constraint. > > John > > [EMAIL PROTECTED] wrote: > > >Hi gurus, > >I need detect and delete duplicate rows in any table, somebody > helpme > >thanks!!! > > > >@lex > > > > Lic. Alexander Ordóñez Arroyo > > Caja Costarricense del Seguro Social > > Soporte Técnico - División de Informática > > Telefono: 295-2004, San José, Costa Rica > > [EMAIL PROTECTED]Icq# 30173325 > > > > > >The true is out there in WWW > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: orantdba > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
Hi, Probably the best way is to attempt to enable a unique constraint and use the exceptions table to find the rowid's all rows that violate the constraint. John [EMAIL PROTECTED] wrote: >Hi gurus, >I need detect and delete duplicate rows in any table, somebody helpme >thanks!!! > >@lex > > Lic. Alexander Ordóñez Arroyo > Caja Costarricense del Seguro Social > Soporte Técnico - División de Informática > Telefono: 295-2004, San José, Costa Rica > [EMAIL PROTECTED]Icq# 30173325 > > >The true is out there in WWW > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
Title: RE: Duplicate rows Delete duplicate records Sanjay Raj 06 Dec 2001, Rating 4.42 (out of 5) Here is a short Oracle script that deletes duplicate records from a table based on the unique columns selected: Rem del_dup.sql Rem Rem Script to delete duplicate rows from a table Rem Rem Enter Table_Name as MY_TABLE Rem And Column_List as COLUMN_1,COLUMN_2,COLUMN_3 ... Column_N Rem (i.e. no spaces) Rem Rem Accept table_name Prompt 'Enter Table Name: ' Accept column_list Prompt 'Enter Column List (no spaces): ' BEGIN LOOP DELETE FROM &table_name WHERE ROWID IN (SELECT MIN (ROWID) FROM &table_name GROUP BY &column_list HAVING COUNT (*) > 1); EXIT WHEN SQL%NOTFOUND; END LOOP; COMMIT; END; / -Original Message- From: Alexander Ordonez [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Subject: Duplicate rows Hi gurus, I need detect and delete duplicate rows in any table, somebody helpme thanks!!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED] Icq# 30173325 The true is out there in WWW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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: Duplicate rows
Delete from table_name where rowid not in(select min(rowid) from table_name group by col1,col2,...coln); Best, Sergey -Original Message- Sent: Wednesday, February 06, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Subject:Duplicate rows Hi gurus, I need detect and delete duplicate rows in any table, somebody helpme thanks!!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
delete from z where rowid in (select rowid from z a where a.rowid > (select min(rowid) from z b where a.x = b.x)); Alexander Ordonez wrote: > > Hi gurus, > I need detect and delete duplicate rows in any table, somebody helpme > thanks!!! > > @lex > > Lic. Alexander Ordóñez Arroyo > Caja Costarricense del Seguro Social > Soporte Técnico - División de Informática > Telefono: 295-2004, San José, Costa Rica > [EMAIL PROTECTED]Icq# 30173325 > > > The true is out there in WWW > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Alexander Ordonez > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
thanks!!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW > -Mensaje original- > De: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Enviado el: Miércoles 6 de Febrero de 2002 10:26 AM > Para: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Asunto: Re: Duplicate rows > > > DELETE FROM a > WHERE a.rowid > (SELECT MIN(b.rowid) > FROM table b > WHERE b.key_field1 = a.keyfield1 > AND b.key_field2 = a.keyfield2 > AND b.key_fieldN = a.keyfieldN); > > Be certain to include all fields in WHERE clause that make up a unique > record. > > Rick > > > > > > Alexander > > Ordonez To: Multiple recipients of > list ORACLE-L <[EMAIL PROTECTED]> > > s.sa.cr> Subject: Duplicate rows > > Sent by: > > root@fatcity. > > com > > > > > > 02/06/2002 > > 10:33 AM > > Please > > respond to > > ORACLE-L > > > > > > > > > > Hi gurus, > I need detect and delete duplicate rows in any table, somebody helpme > thanks!!! > > @lex > > Lic. Alexander Ordóñez Arroyo > Caja Costarricense del Seguro Social > Soporte Técnico - División de Informática > Telefono: 295-2004, San José, Costa Rica > [EMAIL PROTECTED]Icq# 30173325 > > > The true is out there in WWW > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Alexander Ordonez > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
DELETE FROM a WHERE a.rowid > (SELECT MIN(b.rowid) FROM table b WHERE b.key_field1 = a.keyfield1 AND b.key_field2 = a.keyfield2 AND b.key_fieldN = a.keyfieldN); Be certain to include all fields in WHERE clause that make up a unique record. Rick Alexander Ordonez To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Duplicate rows Sent by: root@fatcity. com 02/06/2002 10:33 AM Please respond to ORACLE-L Hi gurus, I need detect and delete duplicate rows in any table, somebody helpme thanks!!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
thanks for your help!!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW > -Mensaje original- > De: Mark Leith [SMTP:[EMAIL PROTECTED]] > Enviado el: Miércoles 6 de Febrero de 2002 10:48 AM > Para: Multiple recipients of list ORACLE-L > Asunto: RE: Duplicate rows > > Alex, > > Here are a couple of scripts that have come from the list in the past: > > === > > declare > cursor get_dups is > select pk_col1, pk_col2, pk_col3, count(*) > from table > group by pk_col1, pk_col2, pk_col3 > having count(*) > 1; > dupRec get_dups%rowtype; > begin > for dupRec in get_dups loop > delete from table > where pk_col1 = dupRec.pk_col1 > and pk_col2 = dupRec.pk_col2 > and pk_col3 = dupRec.pk_col3 > and rownum = 1; > end loop; > end; > / > > === > > Identify duplicate records: > > select COL1, >COL2, >COL#, >COUNT(*) > from . > group by COL1, COL2, COL# > having count(*) > 1; > > Remove duplicate records: > > delete from . a > where rowid < ( > select max(rowid) > from . b > where b.COL1 = a.COL1 >and b.COL2 = a.COL2 >and b.COL# = a.COL# > ); > > === > > Just for giggles, if you want to do this on DB2 as well, then check this > out: > > http://www.searchDatabase.com/tip/1,289483,sid13_gci784575,00.html > > HTH > > Mark > > -Original Message- > Ordonez > Sent: 06 February 2002 15:33 > To: Multiple recipients of list ORACLE-L > > > Hi gurus, > I need detect and delete duplicate rows in any table, somebody helpme > thanks!!! > > @lex > > Lic. Alexander Ordóñez Arroyo > Caja Costarricense del Seguro Social > Soporte Técnico - División de Informática > Telefono: 295-2004, San José, Costa Rica > [EMAIL PROTECTED]Icq# 30173325 > > > The true is out there in WWW > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Alexander Ordonez > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mark Leith > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
Alexander Ordonez wrote:
>
> Hi gurus,
> I need detect and delete duplicate rows in any table, somebody helpme
> thanks!!!
>
> @lex
>
> Lic. Alexander Ordóñez Arroyo
> Caja Costarricense del Seguro Social
> Soporte Técnico - División de Informática
> Telefono: 295-2004, San José, Costa Rica
> [EMAIL PROTECTED]Icq# 30173325
>
>
Check the EXCEPTIONS clause of the ALTER TABLE ... ADD CONSTRAINT
command. Create an EXCEPTIONS table, create a unique constraint on the
columns which *should* be unique, then proceed as follows :
create table nodup
as select distinct * from
where rowid in (select row_id from exceptions
where table_name = upper('');
(I dislike distinct but sometimes ... :-))
delete
where rowid in (select row_id from exceptions
where table_name = upper('');
insert into
select * from nodup;
drop table nodup;
truncate table exceptions;
and go on with the next table. Write a script which takes a parameter
for all this part, no need to retype it each time.
--
Regards,
Stephane Faroult
Oriole Ltd
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Duplicate rows
Courtesy of Ari Kaplan of this list: delete from &&table_name a where rowid > (select min(rowid) from &&table_name b where a.&&column_name=b.&&column_name ) / this works if only one column is duplicated but you should be able to change it (concatenate all the columns together?) if you don't duplicate on just one column --- Alexander Ordonez <[EMAIL PROTECTED]> wrote: > Hi gurus, > I need detect and delete duplicate rows in any table, somebody helpme > > thanks!!! > > @lex > > Lic. Alexander Ordóñez Arroyo > Caja Costarricense del Seguro Social > Soporte Técnico - División de Informática > Telefono: 295-2004, San José, Costa Rica > [EMAIL PROTECTED]Icq# 30173325 > > > The true is out there in WWW > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Alexander Ordonez > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
Or to just count them: SELECT col1,col2.coln,count(*) from table Group by col1,col2...coln Having count(*) >1; -Original Message- Sent: Wednesday, February 06, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Subject: Duplicate rows Hi gurus, I need detect and delete duplicate rows in any table, somebody helpme thanks!!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
Here is a query that was posted here a while ago: delete from pa_answer where rowid in (select rowid from p_answer pa where rowid > (select min(pa2.rowid) from p_answer pa2 where pa.p_id = pa2.p_id and pa.p_name = pa2.p_name)) This should help. Viktor --- Alexander Ordonez <[EMAIL PROTECTED]> wrote: > Hi gurus, > I need detect and delete duplicate rows in any > table, somebody helpme > thanks!!! > > @lex > > Lic. Alexander Ordóñez Arroyo > Caja Costarricense del Seguro Social > > Soporte Técnico - División de Informática > > Telefono: 295-2004, San José, Costa Rica > > [EMAIL PROTECTED]Icq# 30173325 > > > The true is out there in WWW > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Alexander Ordonez > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > 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). __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows
Alex, Here are a couple of scripts that have come from the list in the past: === declare cursor get_dups is select pk_col1, pk_col2, pk_col3, count(*) from table group by pk_col1, pk_col2, pk_col3 having count(*) > 1; dupRec get_dups%rowtype; begin for dupRec in get_dups loop delete from table where pk_col1 = dupRec.pk_col1 and pk_col2 = dupRec.pk_col2 and pk_col3 = dupRec.pk_col3 and rownum = 1; end loop; end; / === Identify duplicate records: select COL1, COL2, COL#, COUNT(*) from . group by COL1, COL2, COL# having count(*) > 1; Remove duplicate records: delete from . a where rowid < ( select max(rowid) from . b where b.COL1 = a.COL1 and b.COL2 = a.COL2 and b.COL# = a.COL# ); === Just for giggles, if you want to do this on DB2 as well, then check this out: http://www.searchDatabase.com/tip/1,289483,sid13_gci784575,00.html HTH Mark -Original Message- Ordonez Sent: 06 February 2002 15:33 To: Multiple recipients of list ORACLE-L Hi gurus, I need detect and delete duplicate rows in any table, somebody helpme thanks!!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Duplicate rows
Hi gurus, I need detect and delete duplicate rows in any table, somebody helpme thanks!!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL to find duplicate rows...
Thank you everyone! We found the culprits Jim __ Jim Hawkins Oracle Database Administrator Data Management Center of Expertise Pharmacia Corporation 800 North Lindbergh Blvd. St. Louis, Missouri 63167 Work (314) 694-4417 Cellular (314) 724-9664 Pager (314) 294-9797 [EMAIL PROTECTED] -Original Message- Sent: Thursday, December 06, 2001 3:35 PM To: Multiple recipients of list ORACLE-L Jim, Oops! Right after I sent this, I saw that you asked to find, not delete, duplicates. Sorry, I've spent the last two days de-duping a bunch of tables, so I've got deletion on the brain. How about: Select SomeColumn >From MyTable Where MyColumn In ( Select MyColumn From My Table Group By MyColumn Having Count(*) > 1 ) ; Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Thursday, December 06, 2001 3:34 PM To: [EMAIL PROTECTED] Jim, An option that handles multiple duplicates is: Delete >From MyTable Where (MyColumn,RowID) In ( Select MyColumn,RowID From MyTable Minus Select MyColumn,Min(RowID) From MyTable Group By MyColumn ); You could use Max(RowID) as well, depending on your needs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- JAMES W [IT/1000] Sent: Thursday, December 06, 2001 2:26 PM To: Multiple recipients of list ORACLE-L Hello all, I know this has come across many times now, and I thought I had it saved somewhere - you know how that goes... I need to query a table to find all duplicate rows based on a given column. Don't feel like re-inventing the wheel... Thanks in advance, Jim -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: HAWKINS, JAMES W [IT/1000] INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL to find duplicate rows...
Jim, An option that handles multiple duplicates is: Delete >From MyTable Where (MyColumn,RowID) In ( Select MyColumn,RowID From MyTable Minus Select MyColumn,Min(RowID) From MyTable Group By MyColumn ); You could use Max(RowID) as well, depending on your needs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- JAMES W [IT/1000] Sent: Thursday, December 06, 2001 2:26 PM To: Multiple recipients of list ORACLE-L Hello all, I know this has come across many times now, and I thought I had it saved somewhere - you know how that goes... I need to query a table to find all duplicate rows based on a given column. Don't feel like re-inventing the wheel... Thanks in advance, Jim -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL to find duplicate rows...
Jim, Oops! Right after I sent this, I saw that you asked to find, not delete, duplicates. Sorry, I've spent the last two days de-duping a bunch of tables, so I've got deletion on the brain. How about: Select SomeColumn >From MyTable Where MyColumn In ( Select MyColumn From My Table Group By MyColumn Having Count(*) > 1 ) ; Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Thursday, December 06, 2001 3:34 PM To: [EMAIL PROTECTED] Jim, An option that handles multiple duplicates is: Delete >From MyTable Where (MyColumn,RowID) In ( Select MyColumn,RowID From MyTable Minus Select MyColumn,Min(RowID) From MyTable Group By MyColumn ); You could use Max(RowID) as well, depending on your needs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- JAMES W [IT/1000] Sent: Thursday, December 06, 2001 2:26 PM To: Multiple recipients of list ORACLE-L Hello all, I know this has come across many times now, and I thought I had it saved somewhere - you know how that goes... I need to query a table to find all duplicate rows based on a given column. Don't feel like re-inventing the wheel... Thanks in advance, Jim -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL to find duplicate rows...
select column1, count(*) from my_table group by column1 having count(*) > 1 __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
SQL to find duplicate rows...
Hello all, I know this has come across many times now, and I thought I had it saved somewhere - you know how that goes... I need to query a table to find all duplicate rows based on a given column. Don't feel like re-inventing the wheel... Thanks in advance, Jim __ Jim Hawkins Oracle Database Administrator Data Management Center of Expertise Pharmacia Corporation 800 North Lindbergh Blvd. St. Louis, Missouri 63167 Work (314) 694-4417 Cellular (314) 724-9664 Pager (314) 294-9797 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: HAWKINS, JAMES W [IT/1000] INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Duplicate rows.
Hi , try this query delete from z where rowid in (select rowid from z a where a.rowid > (select min(rowid) from z b where a.x = b.x)); rukmini - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 24, 2001 9:55 AM > hi lists > > can anybody tell me how to delete duplicate rows in the table. > > my table has 10 rows (5 sets of each 2 records) all are identical. > > how to delete 1 full set. I need the rows value should be distinct. > > I tried > > create table xyz as select distinct and finished the task. > > but this is a lengthy process. > > any short way? > > srinivas > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Tatireddy, Shrinivas (MED, Keane) > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rukmini Devi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Duplicate rows.
hi lists can anybody tell me how to delete duplicate rows in the table. my table has 10 rows (5 sets of each 2 records) all are identical. how to delete 1 full set. I need the rows value should be distinct. I tried create table xyz as select distinct and finished the task. but this is a lengthy process. any short way? srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL PROBLEM for duplicate rows..
Yes, It works .. Thanks.. On Fri, 25 May 2001, [iso-8859-1] Burçin Üstün Kýlýç wrote: > I think this query eliminates the duplicated rows: > > delete from table_name where rowid not in (select maxrowid from (select > column1,column2, max(rowid) maxrowid from den group by column1,column2)); > > - Original Message - > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Friday, May 25, 2001 1:01 PM > > > > Hi > > > > Table A got some duplicated rows.. The requirement is to delete the > > duplicate records by maintaing one copy.. > > Is it manageable thru single query? or have to go for PL/SQL procedure? > > > > Pl. send me ur valuable inputs. > > > > Saravanakumar > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: N. SARAVANA KUMAR > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: =?iso-8859-1?B?QnVy52luINxzdPxuIEv9bP3n?= > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: N. SARAVANA KUMAR INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL PROBLEM for duplicate rows..
I am sorry I made a mistake in naming . Correct one is below: delete from table_name where rowid not in (select maxrowid from (select column1,column2, max(rowid) maxrowid from table_name group by column1,column2)); - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Friday, May 25, 2001 1:22 PM > I think this query eliminates the duplicated rows: > > delete from table_name where rowid not in (select maxrowid from (select > column1,column2, max(rowid) maxrowid from den group by column1,column2)); > > - Original Message - > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Friday, May 25, 2001 1:01 PM > > > > Hi > > > > Table A got some duplicated rows.. The requirement is to delete the > > duplicate records by maintaing one copy.. > > Is it manageable thru single query? or have to go for PL/SQL procedure? > > > > Pl. send me ur valuable inputs. > > > > Saravanakumar > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: N. SARAVANA KUMAR > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: =?iso-8859-1?B?QnVy52luINxzdPxuIEv9bP3n?= > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?B?QnVy52luINxzdPxuIEv9bP3n?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL PROBLEM for duplicate rows..
I think this query eliminates the duplicated rows: delete from table_name where rowid not in (select maxrowid from (select column1,column2, max(rowid) maxrowid from den group by column1,column2)); - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Friday, May 25, 2001 1:01 PM > Hi > > Table A got some duplicated rows.. The requirement is to delete the > duplicate records by maintaing one copy.. > Is it manageable thru single query? or have to go for PL/SQL procedure? > > Pl. send me ur valuable inputs. > > Saravanakumar > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: N. SARAVANA KUMAR > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?B?QnVy52luINxzdPxuIEv9bP3n?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
SQL PROBLEM for duplicate rows..
Hi Table A got some duplicated rows.. The requirement is to delete the duplicate records by maintaing one copy.. Is it manageable thru single query? or have to go for PL/SQL procedure? Pl. send me ur valuable inputs. Saravanakumar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: N. SARAVANA KUMAR INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Uniquely Weird - duplicate rows
Hello all,
8.1.6.2 on HP/UX
11.0
Has anyone seen
something like this before? I can't rebuild the primary key - the error is
duplicate rows found. So I check the data (first listing) and see duplicate rows
via fts. When I give it a hint to use the primary key it shows
nothing. When I dump the rowid's, there are no dups.
Weirdness. I think I'll have to drop the index and create it from
scratch.
SQL> select
partner_id, category_id, relationship_type, count('x') 2
from qode$partner_category_sold 3 group by partner_id,
category_id, relationship_type 4 having count('x') >
1;
PARTNER_ID
CATEGORY_ID RE COUNT('X')-- --- --
--
736844 2921
VE 2
736844 2922
VE 2
736844 3158
VE 2
736844 3160
VE 2
736844 3161
VE 2
736844 3162
VE 2
736844 3163
VE 2
736844 3164
VE 2
736844 3166
VE 2
736844 3233
VE 2
736844 3235
VE 2
736844 3249
VE 2
736844 3253
VE 2
736844 3255
VE 2
736844 3258
VE 2
736844 3262
VE 2
736844 3264
VE 2
736844 3273
VE 2
736844 3283
VE 2
736844 3297
VE 2
736844 3303
VE 2
21 rows
selected.
Execution
Plan--
0 SELECT STATEMENT
Optimizer=CHOOSE 1 0
FILTER 2 1 SORT (GROUP
BY) 3 2
TABLE ACCESS (FULL) OF 'PARTNER_CATEGORY_SOLD'
1
select /*+ index (pcs xpkpartner_category_sold) */ 2 partner_id,
category_id, relationship_type, count('x') 3 from
qode$partner_category_sold pcs 4 group by partner_id,
category_id, relationship_type 5* having count('x') >
1SQL> /
no rows
selected
Execution
Plan--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26
Card=144492 Bytes
=4334760)
1 0 FILTER 2
1 SORT (GROUP BY NOSORT) (Cost=26 Card=144492
Bytes=433476
0)
3 2 INDEX (FULL SCAN) OF
'XPKPARTNER_CATEGORY_SOLD'
(UNIQU E) (Cost=26
Card=144492 Bytes=4334760)
Lisa
Rutland KoivuOracle Database
Administrator
Certified
Self-Important Database Deity
Slayer of Unix
Administrators
Wanton
Kickboxing Goddess
[EMAIL PROTECTED]4850
N.State Road 7, G104
Ft.
Lauderdale, FL 33319Phone: 954-484-3191 x
138Fax: 954-484-2933www.neom.comwww.paperclick.comwww.qode.com
