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.
T-SQL
update t1 set
t1.col2 = t2.col2
from t1
inner join t2 on t1.col1 = t2.col1;
Oracle
update
(select
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.
