CODEWITHSUNDEEP
sqlplsql
Kelv
Kelv

Reputation: 49

Find duplicated row in SQL using Cursor

I am using this loop to update the value one row at time. How do I exit the for loop once it comes across a duplicated value and prints out the duplicated row? The three keys are cust_ref, filter_name, and filter_type.

DECLARE

CURSOR c1
IS
  SELECT cust_ref, filter_name, cust_type
  FROM CUSTOMER
  WHERE cust_ref like 'CUST_REF%';

BEGIN

   FOR e in c1

   LOOP

    UPDATE CUSTOMER
    SET cust_ref = REPLACE (cust_ref, 'CUST_REF', 'UNDER_CUST_REF')
    WHERE cust_ref = e.cust_ref
          and filter_name = e.filter_name
          and cust_type = e.cust_type;  

   END LOOP;

END;

EDIT: I'm recieving this error when doing a regular update statement even when I drop or disable the primary key constraint.

SQL Error: ORA-00001: unique constraint (DB.PRIMARYKEY) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Upvotes: 0

Views: 2114

Answers (1)

Caleth
Caleth

Reputation: 62616

This should show your duplicates, don't use a cursor

SELECT Second.cust_ref, Second.filter_name, Second.cust_type
  FROM CUSTOMER First
  JOIN CUSTOMER Second 
  ON First.cust_ref = REPLACE (Second.cust_ref, 'CUST_REF', 'UNDER_CUST_REF')
  WHERE First.cust_ref like '%CUST_REF%'

Upvotes: 1

Related Questions