This discussion came up on Twitter so I …

This discussion came up on Twitter so I thought I’d blog about it. UPDATE FROM (sometimes called an UPDATE JOIN) is an language construct that is an extension to the ANSI-92 standard supported by several DBMS’s, but not by Oracle. I first discovered this when doing some ETL work in SSIS with an Oracle destination. The Oracle DBA’s answer was to use a cursor, which admittedly has much less of a performance impact in Oracle than it does in SQL Server. But my set-based brain just couldn’t deal with that. So I went digging and discovered a set-based method to use in Oracle to accomplish the same thing.

The following two code snippets show how I would accomplish the same thing in both T-SQL and in Oracle.


update t1 set 
     t1.col2 = t2.col2
from t1
     inner join t2 on t1.col1 = t2.col1;


      t2.col2 as src_2,
      t2.col2 as tgt_2
  from t1
       inner join t2 on t1.col1 = t2.col1
set tgt_2 =src_2;

As you can see, the set-based Oracle version would become quite verbose if you’re dealing with a lot of columns. Also, Oracle requires the subquery to return unique rows, which makes me wonder if it isn’t using a cursor under the hood anyway.

Please share this

Leave a Reply

Related Articles