In Oracle, how to delete a row with a specific value, only if it is a duplicate?

Question

Create Table testpo
( 
    poid Varchar2(10),
    ToDelete Varchar2(10)
);

Insert into testpo values (‘PO1’, ‘D’);
Insert into testpo values (‘PO1’, ‘N’);
Insert into testpo values (‘PO2’, ‘D’);
Insert into testpo values (‘PO3’, ‘D’);

I want to create a stored procedure to return the unique POIds but if there is D and N for same POid, I only need the row with IsDelete = N

Output needed :

PO1 N
PO2 D
PO3 D

Answer 1

If you want to SELECT the unique poid prioritising ToDelete values of N then you can use:

SELECT poid,
       ToDelete
FROM   (
  SELECT poid,
         todelete,
         ROW_NUMBER() OVER (PARTITION BY poid ORDER BY ToDelete DESC) AS rn
  FROM   testpo
)
WHERE  rn = 1

If you want to DELETE the non-unique rows (again, prioritising retaining ToDelete values of N) then you can use:

DELETE FROM testpo
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (PARTITION BY poid ORDER BY ToDelete DESC) AS rn
    FROM   testpo
  )
  WHERE  rn > 1
)

The output from the SELECT and the rows remaining after the DELETE are:

POID	TODELETE
PO1	N
PO2	D
PO3	D

If you want it as a procedure then wrap it in a CREATE PROCEDURE statement and return a SYS_REFCURSOR variable to contain the output.

fiddle