Oracle: Update From equivalent

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.

Technorati Tags: join, ORA-01732, ORA-01779, Oracle, SQL, subquery, UPDATE, UPDATE FROM, WHERE IN

1. LIMIT equivalent for Oracle
2. Spool a CSV file from Oracle/ SQL*Plus without quotes
3. Writing basic case switch statements in Oracle PL/SQL
4. Setting Database Connections in JBoss/ ATG
5. Joining columns/ concatenate strings in Oracle PLSQL/ MySQL
6. Changing the HTTP port Oracle uses
7. Oracle: Delete all rows from all tables etc…