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’
)