SQL UPDATE - DB CAFE

1 UPDATE[편집]

UPDATE ~ SELECT

UPDATE customer SET state='CA';
UPDATE table1
   SET column1 = (SELECT expression1
                   FROM table2
                  WHERE conditions)
[WHERE conditions];

예시)

UPDATE 
       (SELECT e.dname              -- 대상 컬럼
             , d.dname AS dname_new -- 바뀔 컬럼값
          FROM emp  e
             , dept d
         WHERE d.deptno = e.deptno
        )
   SET dname = dname_new -- 
; 
예시2 )
UPDATE 
     ( 
        SELECT GRANTABLE                -- 원본 키
             , 'YES' GRANTABLE_NEW      -- 대체 키 
          FROM TB_MGR_GRANT A 
         WHERE GRANTEE IN (SELECT USERNAME FROM TB_MGR_USER B WHERE B.SCHEMA_YN ='Y') -- 
           AND GRANTABLE <> 'YES'
           AND OBJECT_TYPE = 'TABLE' 
           AND GRANTEE NOT IN ('ERPAPP','OBTHT','ERPHR','TMS','GWINF')                     
     )
  SET GRANTABLE = GRANTABLE_NEW
;

1.1 다중 UPDATE[편집]

UPDATE TB_MIG_SEQ_MGR A
   SET A.TABLE_HAN_NAME = (
            SELECT COMMENTS  FROM DBA_TAB_COMMENTS B
             WHERE B.TABLE_NAME = A.TABLE_NAME  
               AND B.OWNER    = A.OWNER 
           ) 
;

1.2 다중 UPDATE 2nd[편집]

UPDATE 테이블A A
SET ( A.aa, A.bb ) = ( SELECT B.aa, B.bb
                         FROM 테이블 B B
                        WHERE B.id = A.id )
WHERE EXISTS ( SELECT B.aa, B.bb
                 FROM 테이블 B B
                WHERE B.id = A.id )

/*+ BYPASS_UJVC */ 힌트 11g 부터는 Merge Into 로 권고 ,힌트 사용 불가