CODEWITHSUNDEEP
sqloracle-database
user129186
user129186

Reputation: 1238

Removing entirely duplicate rows from all tables in an Oracle database

I'm looking for a query that will remove duplicates from a table, while at the same time being column-agnostic, so it can be iterated over all tables in the database.

Currently, I'm thinking of something like this:

CREATE TABLE TMP_TABLE AS SELECT * FROM EXISTING_TABLE;
TRUNCATE EXISTING_TABLE;
INSERT INTO EXISTING_TABLE (SELECT DISTINCT * FROM TMP_TABLE);
DROP TMP_TABLE;

Is this a good way to do it? Is there a way to do this in-place?

Upvotes: 0

Views: 127

Answers (2)

Farrukh Anique
Farrukh Anique

Reputation: 1

Query for removing duplicate rows in table through self join

DELETE FROM XXPWC_TEST -- Assume table name XXPWC_TEST
WHERE HEADER_ID IN (
SELECT A.HEADER_ID 
FROM XXPWC_TEST A
    ,XXPWC_TEST B
WHERE 1=1
AND A.H_NAME = B.H_NAME -- Join duplicate column name
AND A.H_NAME2 = B.H_NAME2 -- Join duplicate column name
AND A.HEADER_ID > B.HEADER_ID -- Primary key
);

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5141

You can directly delete the duplicates in the original table rather than creating a new temp table and inserting back again.

DELETE * FROM EXISTING_TABLE where row_id not in
(select max(row_id) from EXISTING_TABLE group by col1);  -- Include any one column in group by

Upvotes: 1

Related Questions