Oracle SQL set table entries value based on crationdate and foreignkeys rententionperiod

Question

i have 2 tables for the sake of simplicity i changed the names of tables and removed unneeded columns:

TABLE A (b_ref, created, detected)

TABLE B (id, retention_period)

I am trying to create a procedure which will set the 'A.detected' value for each entry of 'A' to 1 if the 'A.created' Date is older than 'B.retention_period' days.

Based on some tutorials and other threads here, i did this - which is not even compiling:

 1  CREATE OR REPLACE PROCEDURE DETECT_RETENTION_EXPIRE AS 
 2  BEGIN
 3  UPDATE( 
 4  SELECT b_ref, created , b.id , b.retention_period  
 5  FROM A
 6  INNER JOIN B b 
 7  ON b_ref = b.id 
 8  WHERE created <= (SYSDATE - interval b.retention_period day) 
 9  ) a 
 10 SET a.detected = 1;
 11
 12 END DETECT_RETENTION_EXPIRE;

Compiler gives me following ERRORS:

On line 4 - PL/SQL: SQL STATEMENT IGNORED
On line 9 - PL/SQL: ORA_00907: RIGHT BRACKET MISSING

I am not sure what i am doing wrong, can you help me ?

Thanks in advance

Answer 1

You need to use interval '1' day or directly *b.retention_period and a.detected must exists in the SELECT clause follows:

CREATE OR REPLACE PROCEDURE DETECT_RETENTION_EXPIRE AS 
   BEGIN
   UPDATE( 
   SELECT b_ref, created , b.id , b.retention_period, a.detected  
   FROM A
   INNER JOIN B b 
   ON b_ref = b.id 
   WHERE created <= (SYSDATE - b.retention_period ) -- or b.retention_period * interval '1' day 
   ) a 
  SET a.detected = 1;

  END DETECT_RETENTION_EXPIRE;