CODEWITHSUNDEEP
oracle-databaseselectduplicates
Cemre Mengü
Cemre Mengü

Reputation: 18774

Selecting duplicate rows from a table (ignoring the primary key)

I have an Oracle table where I want to find out if there are any duplicate rows (i.e. where all column values are equal). The problem is that the rows have unique primary keys so I want to exclude them since they are basically preventing me doing this.

Is there a way to ignore the primary key when doing such a task (instead of listing all columns except the primary key column) so that I can find out the duplicate rows?

Upvotes: 0

Views: 2191

Answers (2)

wolφi
wolφi

Reputation: 8361

No, just list all columns except the primary key columns in the GROUP BY clause:

CREATE TABLE mytable (
  pk NUMBER PRIMARY KEY,
  c1 NUMBER NOT NULL,
  c2 NUMBER
);

INSERT INTO mytable (pk, c1, c2) VALUES (100, 1, 1);
INSERT INTO mytable (pk, c1, c2) VALUES (101, 1, 1);
INSERT INTO mytable (pk, c1, c2) VALUES (102, 2, 1);
INSERT INTO mytable (pk, c1, c2) VALUES (103, 2, null);
INSERT INTO mytable (pk, c1, c2) VALUES (104, 2, null);

SELECT c1, c2
  FROM mytable
 GROUP BY c1, c2
HAVING COUNT(*) > 1;

C1    C2
----- -----
1     1
2     (null)

To find out the non-primary key columns, you could use the following query. For most tables it will be quicker to type the columns instead of pasting/running the query:

SELECT column_name
  FROM user_tab_columns co
 WHERE co.table_name = 'MYTABLE'
   AND NOT EXISTS (
       SELECT *
         FROM user_constraints  pk
         JOIN user_cons_columns pc USING (owner, constraint_name)
        WHERE pk.table_name = co.table_name
          AND constraint_type='P'
          AND co.column_name = pc.column_name)
 ORDER BY co.column_id;

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231861

You're going to have to list the other columns explicitly.

Potentially, you could use dynamic SQL to generate the query that you want. But that is unlikely to be terribly helpful if this is just for a single table. If you were trying to automate a process of comparing dozens or hundreds of tables, a dynamic SQL approach would potentially be easier to manage.

Upvotes: 0

Related Questions