Quick note on how to update data in a series of joined tables using a subquery in the UPDATE statement in Oracle, kind of like the UPDATE FROM in SqlServer.
First of all check out the above diagram, I’ve stolen it from this link: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/update_statement.htm which also gives you more information on the UPDATE statement.
So what this says is that we can update on a table alias/subquery , which could contain our data set made from a series of joins such as the code snippet below:
update (select tb5.stock_level from schema1.table1 tb1, schema1.table2 tb2, schema1.table3 tb3, schema1.table4 tb4, schema2.table1 tb5 where tb1.id = tb2.id AND tb2.prod_id = tb3.prod_id AND tb3.sku_id = tb4.sku_id AND tb4.sku_id NOT IN (SELECT sku_id FROM schema1.table6) AND tb5.ref_id = tb4.sku_id AND tb1.country_iso_code = 'GB') mySubQuery set mySubQuery.stock_level = 0
Basically the same layout as doing subqueries with a SELECT statement, however, there are a few things to note. First of all if you use a SELECT DISTINCT in your subquery you’ll get an Oracle error ORA-01732 mentioning something about data manipulation not being legal for your table view. Next you may find that you get another error ORA-01779 which is something about modifying a column which maps to a non key-preserved table.
While the above syntax is legal and you can get around the latter error, providing your constraints and foreign keys are declared on your database correctly, a better way to achieve updating records in a joined subquery is to use the WHERE IN clause on your UPDATE statement as below:
UPDATE schema2.table1 SET schema2.table1.stock_level = 0 WHERE schema2.table1.ref_id IN (select tb4.sku_id from schema1.table1 tb1, schema1.table2 tb2, schema1.table3 tb3, schema1.table4 tb4, schema2.table1 tb5 where tb1.id = tb2.id AND tb2.prod_id = tb3.prod_id AND tb3.sku_id = tb4.sku_id AND tb4.sku_id NOT IN (SELECT sku_id FROM schema1.table6) AND tb5.ref_id = tb4.sku_id AND tb1.country_iso_code = 'GB')
Seems just as quick and works (0.14 seconds for 2000 updates out of a 50000+ across separate schemas). Also handy if you dont have access to fix the database constraints etc… (Or if you don’t know how).
This content is published under the Attribution-Noncommercial-Share Alike 3.0 Unported license.
- LIMIT equivalent for Oracle
- Spool a CSV file from Oracle/ SQL*Plus without quotes
- Writing basic case switch statements in Oracle PL/SQL
- Setting Database Connections in JBoss/ ATG
- Joining columns/ concatenate strings in Oracle PLSQL/ MySQL
- Changing the HTTP port Oracle uses
- Oracle: Delete all rows from all tables etc…