오라클 SELECT결과로 UPDATE 하기

오라클 SELECT결과로 UPDATE 하는 쿼리문 예제 입니다. 

 

 

1. 예1

 

SELECT COL_KEY1, COL_UP_COL FROM TB_TBL1 T1 WHERE COL_KEY1 IN('M111','M222','M333');
SELECT COL_KEY1, COL_UP_COL FROM TB_TBL2 T1 WHERE COL_KEY1 IN('M111','M222','M333');

UPDATE TB_TBL2   a
    SET a.COL_UP_COL =  (SELECT b.COL_UP_COL FROM TB_TBL1 b WHERE a.COL_KEY1 =b.COL_KEY1)
WHERE EXISTS
(
    SELECT 1 FROM TB_TBL1 b WHERE a.COL_KEY1 =b.COL_KEY1
)
AND a.COL_KEY1='M111';
 

2. 예2
UPDATE
(
    SELECT a.COL_KEY1, a.COL_UP_COL, b.COL_UP_COL U_COL_UP_COL
    FROM TB_TBL2 a, TB_TBL1 b
    WHERE a.COL_KEY1 = b.COL_KEY1
) 
SET COL_UP_COL = U_COL_UP_COL
WHERE COL_KEY1='M111';