Oracle SQL - How do I update from an Outer Joined Table?

Question

The Problem

I need to write an Update query where my SET references an outer joined table.

I can do this fairly easily with SQL Server but I'm having a heck of a time figuring out the syntax in Oracle as I'm only allow a single table in an update query.

I have written the following Oracle query:

UPDATE SalesExt_tmp tmp
SET slsrep = (SELECT three_dig_rep 
              FROM dw_sls_rep_conv sls
              WHERE sls.aims_rep = tmp.slsrep)
WHERE EXISTS (SELECT three_dig_rep
              FROM dw_sls_rep_conv sls
              WHERE sls.aims_rep = tmp.slsrep)
          AND tmp.sysind = 'AIM';

This takes care of the intersection but I need to deal with values in SalesExt_tmp that do not have equivalent matches in dw_sls_rep_conv (I plan to add a case statement to set null values to a default value). To do this I need to set up dw_sls_rep_conv as an outer joined table. But this is where I get stuck.

SQL Server Example

In SQL Server the solution is a piece of cake as you can have multiple tables in an Update Query:

UPDATE SalesExt_tmp tmp 
LEFT JOIN dw_sls_rep_conv sls ON sls.aims_rep = tmp.slsrep 
SET tmp.slsrep = sls.three_dig_rep
WHERE tmp.sysind = 'AIM';

But I can't for the life of me figure out how to do this in Oracle. I understand that this query will allow my slsrep field to be set to NULL in some occasions which causes me to fear that this operation may not be allowed.

Questions

1) Firstly is this possible in Oracle? (It's got to be, right?)

2) How do I need to restructure my query to pull this off? I'm guessing my WHERE EXISTS clause needs to go... but I'm still stuck as to where to place my JOIN.

Solution 1

If I understood you correctly, you want to set the value to NULL if there is no match in the dw_sls_rep_conv table? If so, just drop your EXISTS condition and all the rows will be updated:

UPDATE SalesExt_tmp tmp
SET slsrep = (SELECT three_dig_rep 
              FROM dw_sls_rep_conv sls
              WHERE sls.aims_rep = tmp.slsrep)
WHERE tmp.sysind = 'AIM';

If there is a match in the dw_sls_rep_conv table, then the slsrep column will be updated with selected value, otherwise, with NULL.

Solution 2

Have you considered using a subquery in your where clause that performs the outer join as you stated like this:

UPDATE SalesExt_tmp tmp 
SET slsrep = (SELECT three_dig_rep 
FROM dw_sls_rep_conv sls WHERE sls.aims_rep = tmp.slsrep)      
WHERE 
 tmp.rowid in 
(SELECT tmp1.rowid 
 FROM SalesExt_tmp tmp1, 
      dw_sls_rep_conv sls 
 WHERE 
 tmp1.slsrep = sls.aims_rep (+) 
 AND tmp1.sysind = 'AIM' ); 

Comments

[DanK]

Oh wow.... that was so easy! This does exactly what I wanted to do... Thank you! I'm still trying to get my brain wrapped around the differences in logic between SQL Server and Oracle Update Queries. But I get how this works now.... my WHERE condition sets what is to be updated and my SET defines the values to update to. Any difference in between will be set to null.

[DanK]

Thanks, I started exploring this path shortly after I posted and this will definitely work as well though not quite as efficient. Appreciate the post!