How to delete duplicate records in oracle

In Oracle database, due to the complexity and constant changes of data, duplicate records often occur, which can lead to system errors and affect the integrity and accuracy of the data. Therefore, it is crucial to remove duplicate records in Oracle database.

The following will introduce some effective methods that can help you quickly delete duplicate records.

1. Use the DISTINCT keyword

Method 1: Use the "DISTINCT" keyword to quickly delete duplicate records from a table. You can use the following SQL statement:

DELETE FROM table_name
WHERE rowid NOT IN (SELECT MIN(rowid) FROM table_name GROUP BY column1, column2, ..., column_n);

In this SQL statement, the "DISTINCT" keyword combines the columns into a simple set-like to find duplicate records. SELECT and GROUP BY statements are used to find duplicate records and delete them to ensure data accuracy.

Note: For a single column, you can use the following SQL code:

DELETE FROM table_name
WHERE rowid NOT IN (SELECT MIN(rowid) FROM table_name GROUP BY column_name);

2. Use the ROW_NUMBER() function

Method 2: The ROW_NUMBER() function can be identified using partitioning technology Duplicate records and delete them:

DELETE FROM table_name
WHERE rowid IN (
  SELECT row_id FROM (
    SELECT rowid row_id,
    ROW_NUMBER() OVER (PARTITION BY column2, column3 ORDER BY column1) duplicate_records_count
    FROM table_name
  ) WHERE duplicate_records_count > 1
);

In this SQL statement, the "ROW_NUMBER()" function is used to identify duplicate records and mark the first duplicate record as "1" to help delete the other Duplicate marked records. Partitioning technology groups identical records and sorts them based on the groups to ensure the correctness of the data.

3. Use EXISTS subquery

Method 3: EXISTS subquery can use the WHERE clause to delete duplicate records:

DELETE table_name a
WHERE EXISTS (
  SELECT 1 FROM table_name b
  WHERE b.column1 = a.column1 AND b.column2 = a.column2 AND b.column3 = a.column3 AND b.rowid > a.rowid
);

In this SQL statement, "EXISTS" The subquery searches for records with the same value in the current row (i.e. "a"). Since they are duplicate records, their ROWIDs are different. Therefore, as long as ROWID a is greater than ROWID b, duplicate records can be deleted.

4. Use set operators

Method 4: The set operator can be combined with the "SELECT" and "UNION ALL" operators to find and delete duplicate records.

DELETE FROM table_name
WHERE ROWID IN (
  SELECT ROWID FROM (
    SELECT column1, column2, column3, COUNT(*) duplicates
    FROM table_name
    GROUP BY column1, column2, column3
    HAVING COUNT(*) > 1
    UNION ALL
    SELECT column1, column2, column3, COUNT(*) duplicates
    FROM table_name
    GROUP BY column1, column2, column3
    HAVING COUNT(*) = 1
  ) a
  WHERE a.duplicates > 1
)

In this SQL statement, the "UNION ALL" operator combines two SELECT statements to find duplicate records and non-duplicate records. The HAVING clause is used to filter out records with duplicate counts and delete them.

In summary, different methods and techniques can be used to remove duplicate records in Oracle database. Just easily choose the appropriate method based on your needs. While deleting duplicate records, pay attention to protecting the integrity and accuracy of system data.

The above is the detailed content of How to delete duplicate records in oracle. For more information, please follow other related articles on the PHP Chinese website!