CODEWITHSUNDEEP
oracle-databasestored-procedures
coder247
coder247

Reputation: 2943

simple oracle stored procedure example for updating two tables in one go

Could anyone give a simple example of an Oracle stored procedure for updating two tables in a single go.

Upvotes: 1

Views: 20155

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

I supposed that you update tables a and b with values from c. This is PL/SQL

create or replace procedure update_one_scan as
  cursor c is 
   select a.rowid r1, b.rowid r1, c.value_to_get
   from a join b on (join conditions)
     join c on (join conditions)
   where conditions;
begin
  for r in c
  loop
    update a set col_to_update=r.value_to_get where rowid=r1;
    update b set col_to_update=r.value_to_get where rowid=r2;
  end loop;
end;

You have the advantage of single scan of source tables.

UPDATE: You can do it even in oracle SQL, but is more restrictive(you'll see when you try). But this can be faster.

Is a UPDATE SELECT statement:

Create or replace Procedure update_select AS
BEGIN
  update
  (select a.col_to_update as c1, b.col_to_update as c2, c.value_to_get v1
         from a join b on (join conditions)
           join c on (join conditions)
         where conditions)
  set 
  c1 = v1, c2 = v2;
END;

Upvotes: 2

schurik
schurik

Reputation: 7928

CREATE OR REPLACE PROCEDURE update_2_tables
IS
begin
  update t1 set c1 = 1;
  update t2 set c1 = 2;
end;
/

Upvotes: 6

Related Questions