Asp.net,SQL tips-Tricks for error resolution version 2014,2012,2008,2005,Procedural language,Stored Procedure, computer , excel macro programming,Film,Musical notations,Indian ,Western Music.

May 11, 2013

Joining Two Tables in the Update Statement in Oracle 11g

Method 1:
update tab a
set col1 = -1
where exists (select 1 from tab b
where a.col2 = b.col2
and a.col3 = b.col3
and a.col3 = b.col4 )

Method 2:
update tab a
set (col1,col7,col8) = (select -1, col7,col8 from tab b
where a.col2 = b.col2
and a.col3 = b.col3
and a.col3 = b.col4 )
where exists (select 1 from tab b
where a.col2 = b.col2
and a.col3 = b.col3
and a.col3 = b.col4 )


Note: In Method 2 the sub-query must return only 1 row.
set (col1,col7,col8) = (select -1, col7,col8 from tab b
where a.col2 = b.col2
and a.col3 = b.col3
and a.col3 = b.col4 )