How to retrieve only one piece of duplicate data in oracle

Oracle steps to only fetch a piece of duplicate data: 1. Use the SELECT statement combined with the GROUP BY and HAVING clauses to find duplicate data; 2. Use ROWID to delete duplicate data to ensure that accurate duplicate data is deleted. records, or use the "ROW_NUMBER()" function to delete duplicate data, which will delete all records except the first record in each set of duplicate data; 3. Use the "select count(*) from" statement to return the number of deleted records Guaranteed results.

#The operating environment of this tutorial: Windows 10 system, Oracle version 19c, Dell g3 computer.

When using Oracle database, duplicate data records often appear. These duplicate records not only waste storage space, but may also interfere with data operations. Therefore, removing duplicate data becomes necessary. This article will introduce how to use Oracle's SQL statement to remove duplicate data and retain only one record.

Understand the table structure of duplicate data

Before starting the operation, we need to first understand the structure of the table where the duplicate data is located. By looking at the table definition, you can determine which column values ​​are considered duplicate data and how this is determined.

1. Find duplicate data

Use the SELECT statement combined with the GROUP BY and HAVING clauses to find duplicate data. The specific syntax is as follows:

SELECT 列1, 列2, 列N, COUNT(*) FROM 表名
GROUP BY 列1, 列2, 列N
HAVING COUNT(*) > 1;

This SQL statement will return all records of duplicate data. Each row contains the column value of the duplicate data and the total number of duplicates.

2. Delete duplicate data

There are many ways to delete duplicate data. The following are two common methods:

1. Use ROWID to delete duplicate data:

DELETE FROM 表名 WHERE ROWID NOT IN
(SELECT MAX(ROWID) FROM 表名
GROUP BY 列1, 列2, 列N);

This SQL statement will delete all records except the last record of each set of duplicate data. Using ROWID ensures that exact duplicate data records are deleted.

2. Use the ROW_NUMBER() function to delete duplicate data:

DELETE FROM 表名 WHERE ROWID IN
(SELECT ROWID FROM
(SELECT ROWID, ROW_NUMBER() OVER(PARTITION BY 列1, 列2, 列N ORDER BY 列1) AS rn FROM 表名)
WHERE rn > 1);

This SQL statement will delete all records except the first record in each set of duplicate data. The ROW_NUMBER() function sorts according to the specified column, and then cooperates with the PARTITION BY clause to implement partitioning to ensure that only the first record is retained.

3. Verify deletion results

After deleting duplicate data, we need to verify the results of the operation to ensure that no other important data was accidentally deleted.

SELECT COUNT(*) FROM 表名;

This SQL statement will return the number of records after deduplication. If the result is significantly different from the previous total number of duplicates, it may be due to an error in the deletion operation.

Summary:

Removing duplicate data is one of the common tasks in Oracle database management. This article introduces the steps to use Oracle's SQL statements to remove duplicate data, including detailed steps such as finding duplicate data, deleting duplicate data, and verifying the results. By rationally applying these techniques, you can effectively clean duplicate data in the database and improve the quality and reliability of the data.

The above is the detailed content of How to retrieve only one piece of duplicate data in oracle. For more information, please follow other related articles on the PHP Chinese website!