Comparing records from same table and deleting the old ones

oracleplsql

Greeting,
I have a beginner experience with programming in Oracle PL/SQL. The problem I am trying to solve is that in a hypothetical situation, I want to compare records in the same table based on the arbitrary columns called effdt(effective date) and position_nbr(position number). They would be based on new and old records. If the the new effdt field is greater than or equal to(>=) the old effdt based on the old and new position_nbr being equal, I want to delete the old effdt record from the table. Below is the small procedure I have developed to execute.

DECLARE 
table_cnt_001           NUMBER;
CURSOR ITR1
IS 
(SELECT *
FROM
TABLE1 O
INNER JOIN TABLE1 N
ON
  N.POSITION_NBR = O.POSITION_NBR
WHERE
  N.EFFDT >= O.EFFDT);
BEGIN
SELECT COUNT(*) INTO effdt_cnt_001 FROM TABLE1;
--Checking if table is empty
IF table_cnt_001 !=0 THEN

FOR IT IN ITR1 LOOP
--BEGIN
IF (N.EFFDT >= O.EFFDT) AND (N.POSITION_NBR = O.POSITION_NBR) THEN
DELETE FROM TABLE1 O 
WHERE EXISTS(SELECT O.EFFDT FROM TABLE1 O);
END IF;
COMMIT;
--END;
END LOOP;
END IF;
END;

I have gotten the error identifier 'N.EFFDT' must be declared. As far as I am concerned, I am refencing the cursor in the for loop and I am not sure why the if statement requests for this column to be declared. Please let me know what could be wrong with this logic or if there is a better way to solve this problem. Thanks in advance.

Best Answer

You don't need PL/SQL for this, you can delete using a sub-query:

delete from table1 where ID in (
  select ID from (
    select ID
      ,count(*) over(partition by POSITION_NBR, EFFDT) as cnt
      from table1
    )
  where cnt > 1 --more than one row matches the grouping condition
);
Related Question