Re: Searching for dupes
Date: 15 Jan 2002 16:06:10 -0800
Message-ID: <da20daf0.0201151606.504ace42_at_posting.google.com>
I found this on metalink ID : 1014381.102
hth
Chris
Background
This article contains information on how to delete duplicate rows in a table using a PL/SQL procedure or block.
The duplicate row problem occurs only when primary keys are not declared on table.
Procedures
Create a table test with two fields as follows:
SQL> CREATE TABLE test( col1 NUMBER, col2 VARCHAR2(10));
Insert the following records into the test table as follows:
INSERT INTO test VALUES(10, 'asd'); INSERT INTO test VALUES(10, 'asd'); INSERT INTO test VALUES(10, 'asd'); INSERT INTO test VALUES(20, 'zx'); INSERT INTO test VALUES(30, 'zx');
When you select the data from test table it appears as follows:
SQL> select * from test;
COL1 COL2
--------- ----------
10 asd
10 asd
10 asd
20 zx
30 zx
Example 1
This procedure deletes duplicate records with respect to 'col1' as follows:
SQL> _at_ dup1.sql
CREATE OR REPLACE PROCEDURE dup1
AS
BEGIN
DELETE FROM test WHERE
ROWID NOT IN ( SELECT MIN(ROWID) FROM test
GROUP BY col1);
END dup1;
/
SHOW ERRORS SQL> exec DUP1 gives the following :
SQL> select * from test;
COL1 COL2
----- ----------
10 asd
20 zx
30 zx
Example 2
This procedure deletes duplicate records with respect to 'col2' as follows:
SQL> _at_ dup2.sql
CREATE OR REPLACE PROCEDURE dup2
AS
BEGIN
DELETE FROM test WHERE
ROWID NOT IN ( SELECT MIN(ROWID) FROM test
GROUP BY col2);
END dup2;
/
SHOW ERRORS SQL> exec DUP2 gives the following:
SQL> select * from test;
COL1 COL2
--------- ----------
10 asd
20 zx
Example 3
This procedure deletes duplicate records with respect to both 'col1' and 'col2' as follows:
CREATE OR REPLACE PROCEDURE dup12
AS
BEGIN
DELETE FROM test WHERE
ROWID NOT IN ( SELECT MIN(ROWID) FROM test
GROUP BY col1, col2);
END dup12;
/
SHOW ERRORS SQL> exec DUP12 gives the following:
SQL> select * from test;
COL1 COL2
--------- ----------
10 asd
20 zx
30 zx
"HDog" <abc_at_def.com> wrote in message news:<xjY08.27235$Vq.267878_at_rwcrnsc53>...
> Hello, I was wondering if anyone could explain how to approach
> this scenario.
>
> I have a table that is built as follows
>
> rowid model_num seq_num desc
> ==== ========= ======= ====
> 22 388 7 plate
> 23 388 8 box
> 24 125 3 steel
> 25 388 9 tool
> 26 388 8 glass
> 27 125 4 belt
>
> Rowid is unique. No model_num should
> have a duplicate sequence number, but
> different model_nums can have same seq_num.
>
> In this case row 23 and 26 have the same model_num
> and share a seq_num (8). What is the best way to search for
> this condition.
>
> Would a query be the best way or would a stored procedure
> or block of code work better? I'd say the size of the table is about 100k
> rows.
>
> Thanks,
> Dave
Received on Wed Jan 16 2002 - 01:06:10 CET