CODEWITHSUNDEEP
oracle-databaseplsql
Pierre-olivier Gendraud
Pierre-olivier Gendraud

Reputation: 1927

How to update a table with a function using the same table but which uses only columns not modified by this update

CREATE TABLE A (
    A1 integer,
    A2 integer,
    A3 integer
 );

create function f(a1_ in integer) return integer
is
   ret integer;
begin
    select a3+1 into ret from A where a1=a1_;
    return ret;
end;

INSERT INTO A SELECT 1 a1,1 a2,1 a3 FROM dual union all select 2 a1, 2 a2, 2 a3 from dual

update A t1
   set a3 = 
       (select f(a1) from A t2 where t1.a1=t2.a1);

A is mutating, trigger/function may not see it

This code doesn't work. contrary to

update A t1
   set a3= 
       (select  f(a1) from A t2 where t1.a1=t2.a1);

The problem is the following. I'm using a function using the same table to update the table. But the columns that I'm using in these table are not modified by f. I'm using only a1 to return the next value of a3. And a1 isn't modified in this statement

Is there a way to do these operation. (perhaps using a keyword or something else)

code

Upvotes: 0

Views: 26

Answers (1)

MT0
MT0

Reputation: 168301

You can use PL/SQL:

DECLARE
  v_a1s SYS.ODCINUMBERLIST;
  v_fs  SYS.ODCINUMBERLIST;
BEGIN
  SELECT a1, f(a1)
  BULK COLLECT INTO v_a1s, v_fs
  FROM  A
  FOR UPDATE OF a3;
  
  FORALL i IN 1 .. v_a1s.COUNT
    UPDATE A
    SET   a3 = v_fs(i)
    WHERE a1 = v_a1s(i);
END;
/

db<>fiddle here

Upvotes: 1

Related Questions