oracle delete duplicate data

Oracle database is a very stable database management system with powerful functions and stable performance. In our daily work, we often encounter situations where data queries and data operations are blocked due to duplicate data. This article will introduce how to use Oracle to delete duplicate data.

1. What is duplicate data?

In the database, we often find that some data are repeated, that is, the same data appears multiple times. These data will bring great difficulties to our queries and operations. In Oracle database, we can use deduplication operation to delete these duplicate data.

2. How to delete duplicate data?

Method 1: Using ROWID

The first method is to delete through ROWID. ROWID is a unique identifier assigned to each record in the Oracle system, so we can accurately delete specified duplicate records through ROWID.

As shown below:

DELETE FROM table_name A
WHERE A.ROWID >

ANY (SELECT B.ROWID
         FROM table_name B
        WHERE A.column1 = B.column1
          AND A.column2 = B.column2
          AND A.column3 = B.column3
          AND A.column4 = B.column4);

Among them, table_name is the name of the table where duplicate data needs to be deleted, column1, column2 , column3, column4 are the column names that need to be compared.

Method 2: Use grouping operation

Another method is to delete duplicate data through grouping operation. Grouping operation is a commonly used data operation method in Oracle database. It can group records with the same attributes and perform the same operation on each group.

As shown below:

DELETE FROM table_name
WHERE rowid IN

(SELECT max(rowid)
 FROM table_name
 GROUP BY column1, column2, column3, column4
 HAVING count(*) > 1);

Among them, table_name is the name of the table where duplicate data needs to be deleted, column1, column2, column3, column4 is the column name that needs to be compared.

3. How to prevent duplicate data?

In addition to deleting duplicate data, we should also pay attention to how to prevent the occurrence of duplicate data. The following are some methods to prevent duplicate data:

Method 1: Add unique constraints

When creating a table, you can add unique constraints to ensure the uniqueness of certain columns and prevent duplicate data 's appearance. For example:

CREATE TABLE table_name
(column1 varchar2(10) NOT NULL,
column2 varchar2(10) NOT NULL,
column3 varchar2(10) NOT NULL,
column4 varchar2 (10) NOT NULL,
PRIMARY KEY(column1, column2, column3, column4));

In this way, when trying to insert a record with the same column values ​​​​in a row in the table, the database will report an error , to prevent data duplication.

Method 2: Use triggers

Another method is to use triggers to make corresponding judgments and operations when inserting or updating records to ensure the uniqueness of the data. For example:

CREATE TRIGGER trigger_name
BEFORE INSERT OR UPDATE ON table_name
FOR EACH ROW
DECLARE

v_exists NUMBER;

BEGIN

SELECT COUNT(*)
INTO v_exists
FROM table_name
WHERE column1 = :NEW.column1
  AND column2 = :NEW.column2
  AND column3 = :NEW.column3
  AND column4 = :NEW.column4;

IF v_exists > 0 THEN
  RAISE_APPLICATION_ERROR(-20001, 'Duplicate record found.');
END IF;

END;

This trigger will be executed before the insert and update operations to determine whether the newly inserted record is a duplicate of an existing record. If so, an error will be thrown.

Summary:

In Oracle database, deleting duplicate data is a relatively common task. We can delete it through ROWID or grouping operations. Additionally, we can prevent duplicate data by adding unique constraints and using triggers. All the above methods can make our data operations more stable and secure.

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