Oracle insert into select for update

135 Views Asked by At

Let's say I've got two tables t1 and t2, and I'm inserting data into t1 using columns selected from t2.

create or replace package body for_update_test
as
  procedure test(out_response out varchar2) as
  v_id number;
    begin
      select id into v_id from t2 where id = 1 for update;
      insert into t1
      (
        id
      )
      select
        id
      from 
        t2
      where
        id = 1
      ;
      out_response:= 'success';
  end test;
end for_update_test;
/

The above works, but is there a way to combine the insert-select with for update, something like (it obviously does not work, so wondering if it's just the syntax I am not getting right or if it's not supported):

create or replace package body for_update_test
as
  procedure test(out_response out varchar2) as
  v_id number;
    begin
      insert into t1
      (
        id
      )
      select
        id
      from 
        t2
      where
        id = 1
      for update
      ;
      out_response:= 'success';
  end test;
end for_update_test;
/

Here's what I am trying to achieve: select data from a table (t2 here), modify the values, then insert into its gtt twin (t1 here). Then, the gtt data will be applied back into the actual table itself. The for update during the initial selection from t2 ensures the data written back to it eventually through t1 doesn't accidentally overwrite data written by a different procedure that also modifies (different columns) this way.

Way convoluted than it should be this being a legacy routine, but I've got very little leeway right now to change the whole design (like not having to go through GTT), so just locking the original table for updates to do controlled changes.

0

There are 0 best solutions below