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
? (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
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
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
;
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
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
. -- Kernighan
[EMAIL PROTECTED]
e.ny.us To:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: PL/SQL
Question:Eliminate duplicate rows
by: cc:
[EMAIL PROTECTED]Subject: RE: PL/SQL
Question:Eliminate duplicate rows
.com
09/19/2003 01:54
PM
Please respond
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:
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
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
]
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate
duplicate rows
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
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
:
[EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate
duplicate rows
.com
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
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
] 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
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
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
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
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
Subject: Duplicate rows
Sent by:
root@fatcity
]; [EMAIL PROTECTED]
Asunto: Re: Duplicate rows
DELETE FROM table a
WHERE a.rowid (SELECT MIN(b.rowid)
FROM table b
WHERE b.key_field1 = a.keyfield1
AND b.key_field2
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
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
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
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
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
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
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
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.
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
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
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
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
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:
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:
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
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));
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
41 matches
Mail list logo
