Update Select SQL

Update Select SQL

Syntax SQL : Update Table a

set(a.column1, a.column2,a…) = (select column1, column2,… from table b where b.key = a.key)

where exists (select ‘X’ from table b where b.key = a.key)

Example1 : Update Select

Update course a set (a.course,a.start_date) = (select b.course , b.start_date from course  b where b.course=a.course and b.course like ‘A%’)

where exists  (select ‘X’ where b.course=a.course and b.course like ‘A%’)

Example 2 : Update Select

UPDATE PS_TEST_TABLE a SET (a.FLAG1 ,a.DT_TM1, a.DT_TM2) =

( SELECT ‘P’,SYSDATE,SYSDATE FROM PS_TEST_TABLE b
WHERE b.FLAG2 =’Y’ AND b.SUBMIT_FLAG <>’S’
AND b.EMPLID = a.emplid AND b.seqnum = a.seqnum AND b.YEAR =a.YEAR AND b.YEAR = ‘2017’
)

WHERE exists

( SELECT ‘X’ FROM PS_TEST_TABLE b
WHERE b.FLAG2 =’Y’ AND b.SUBMIT_FLAG <>’S’
AND b.EMPLID = a.emplid AND b.seqnum = a.seqnum AND b.YEAR =a.YEAR AND b.YEAR = ‘2017’
)