Updating multiple tables in pl sql

29-Jan-2017 18:17

However; this is clearly not applicable to your situation, multiple statements would work much better.

What you might consider is "on update cascade", this way you would update the table with the cities with new zipcode and the customers and employees would get updated automatically.

UPDATE summary_data SET current_category = (SELECT category_id FROM products WHERE products.product_id = summary_data.product_id) WHERE EXISTS (SELECT category_id FROM products WHERE products.product_id = summary_data.product_id); If you want to test your skills using the SQL UPDATE statement, try some of our practice exercises.

These exercises allow you to try out your skills with the UPDATE statement.

thanks Helen" id="ctl00_m_m_i_ctl00_gr_ctl11_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5190984" / I use this method: update a,b set a.col5=-1,a.col7=b.col7, a.col8 = b.col8 where a.col1 =b.col1 and a.col2=b.col2 it works much faster then using "exists" if there are indexes on link columns.08/12/2015 : SQL SELECT * 2 FROM product_parent_relation_batch b 3 INNER JOIN product p 4 ON b.prd_id = p.prd_id 5 WHERE p.prd_id = 990; PRD_ID PRD_PARENT_ID P PRD_ID PRD PARENT_PRD_ID ---------- ------------- - ---------- --- ------------- 990 789 F 990 New BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE product pr SET parent_prd_id = (SELECT b.prd_parent_id FROM product_parent_relation_batch b INNER JOIN product p ON b.prd_id = p.prd_id WHERE b.processed = 'F' AND pr.prd_id = p.prd_id) WHERE prd_id in (SELECT p.prd_id FROM product_parent_relation_batch b INNER JOIN product p ON b.prd_id = p.prd_id WHERE b.processed = 'F'); UPDATE product_parent_relation_batch pb SET processed = 'T' WHERE pb.prd_id IN (SELECT b.prd_id FROM product_parent_relation_batch b INNER JOIN product p ON b.prd_id = p.prd_id WHERE b.processed = 'F' AND pb.prd_id = p.prd_id); COMMIT; END; One is physically restricted from updating multiple tables in the same query.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.I didn't test it - but: the SQL language allows updatable views.If you look here, you will see that Oracle indeed supports inherently updatable views.

thanks Helen" id="ctl00_m_m_i_ctl00_gr_ctl11_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5190984" / I use this method: update a,b set a.col5=-1,a.col7=b.col7, a.col8 = b.col8 where a.col1 =b.col1 and a.col2=b.col2 it works much faster then using "exists" if there are indexes on link columns.

08/12/2015 : SQL SELECT * 2 FROM product_parent_relation_batch b 3 INNER JOIN product p 4 ON b.prd_id = p.prd_id 5 WHERE p.prd_id = 990; PRD_ID PRD_PARENT_ID P PRD_ID PRD PARENT_PRD_ID ---------- ------------- - ---------- --- ------------- 990 789 F 990 New BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE product pr SET parent_prd_id = (SELECT b.prd_parent_id FROM product_parent_relation_batch b INNER JOIN product p ON b.prd_id = p.prd_id WHERE b.processed = 'F' AND pr.prd_id = p.prd_id) WHERE prd_id in (SELECT p.prd_id FROM product_parent_relation_batch b INNER JOIN product p ON b.prd_id = p.prd_id WHERE b.processed = 'F'); UPDATE product_parent_relation_batch pb SET processed = 'T' WHERE pb.prd_id IN (SELECT b.prd_id FROM product_parent_relation_batch b INNER JOIN product p ON b.prd_id = p.prd_id WHERE b.processed = 'F' AND pb.prd_id = p.prd_id); COMMIT; END; One is physically restricted from updating multiple tables in the same query.

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.

I didn't test it - but: the SQL language allows updatable views.

If you look here, you will see that Oracle indeed supports inherently updatable views.

CONTRACT_ID); UPDATE wr00262_catalogue_201310 ct SET PORTFOLIO_ID = ( SELECT ch.