Updating multiple tables in pl sql

Secondly: there is no order by clause so this will occur in an unpredictable manner (i.e. simplest contrived way would be (select min(name)...)Long story short: if you can at all avoid it, never ever EVER use any kind of LOOP in a T-SQL statement.Personally, if it wasn't for the 0.001% of the time where there's no other solution, I don't even think it should even be an available function in T-SQL.Drop me an email and I will promptly and gladly rectify it.

Firstly: if the subselect was returning multiple values, then the for loop will be overwriting the name on table2 multiple times for some/all records (not clean). Assuming the outcome of the for loop was intended, the original subselect could have been rewritten in some controlled way to return only 1 value for each record...A working solution for this kind of scenario is to create an application - PL/SQL or otherwise, to grab information for both tables you need to update, iterate through the results, and update the tables in individual statements in each iteration.create table product (prd_id, 2 prd_name, 3 parent_prd_id) 4 as 5 select 123, 'Foo', cast(null as number) from dual union all 6 select 456, 'Bar', null from dual union all 7 select 789, 'Baz', null from dual; Table created SQL create table product_parent_relation_batch 2 (prd_id, 3 prd_parent_id, 4 processed) 5 as 6 select 555, 888, 'T' from dual union all 7 select 123, 789, 'F' from dual union all 8 select 456, 789, 'F' from dual; Table created SQL BEGIN 2 UPDATE product pr 3 SET parent_prd_id = 4 (SELECT b.prd_parent_id 5 FROM product_parent_relation_batch b 6 INNER JOIN product p ON b.prd_id = p.prd_id 7 WHERE b.processed = 'F' 8 AND pr.prd_id = p.prd_id) 9 WHERE prd_id in (SELECT p.prd_id 10 FROM product_parent_relation_batch b 11 INNER JOIN product p ON b.prd_id = p.prd_id 12 WHERE b.processed = 'F'); 13 UPDATE product_parent_relation_batch pb 14 SET processed = 'T' 15 WHERE pb.prd_id IN (SELECT b.prd_id 16 FROM product_parent_relation_batch b 17 INNER JOIN product p ON b.prd_id = p.prd_id 18 WHERE b.processed = 'F' 19 AND pb.prd_id = p.prd_id); 20 COMMIT; 21 END; 22 / PL/SQL procedure successfully completed SQLConsidered it, but I'm going with the cursor because it takes a snapshot of the two tables and updates data without race conditions.I think if a record came in to product_parent_relation_batch while the update product in lines 2-12 was running, product would never get its parent relation because it would then be marked as updated.Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. Update data in table A based on two or more common columns in table B.

Leave a Reply