[Oracle/Tibero] 여러 개의 테이블을 조인해서 업데이트 (2개 이상)

업데이트할 값이 여러 테이블에 흩어져 있을 경우, 조인해서 한 번에 업데이트하는 방법을 소개하겠습니다.

 

[ Contents ]

 

 

0. 오류

UPDATE EMP_TABLE A, INFO_TABLE B
SET
	A.DEPT_NM = B.DEPT_NM
    , A.DEPT_CD = B.DEPT_CD
WHERE
	A.EMP_NUMB = B.EMP_NUMB

 마치 SELECT문을 작성하듯이, 그저 테이블을 조인해서 업데이트할 수 없습니다.

 UPDATE는 하나의 테이블만 가능하며, 위와 같이 UPDATE 뒤에 여러 테이블을 나열할 수 없습니다.

 

 

1. 서브쿼리 활용

UPDATE EMP_TABLE A
SET
	(A.DEPT_NM
    , A.DEPT_CD
    ) = (SELECT B.DEPT_NM
    	, B.DEPT_CD
        FROM INFO_TABLE
        WHERE A.EMP_NUMB = B.EMP_NUMB)

 가장 쉬운 방법은 서브쿼리 활용입니다. SET 문에 서브쿼리를 활용해서 임의의 값이나 다른 테이블의 값을 호출해서 갱신할 수 있습니다.

 

 

UPDATE EMP_TABLE A
SET
	(A.DEPT_NM
    , A.DEPT_CD
    ) = (SELECT B.DEPT_NM
    	, B.DEPT_CD
        FROM INFO_TABLE
        WHERE A.EMP_NUMB = B.EMP_NUMB)
WHERE
	DEPT_NM IS NOT NULL
    AND DEPT_CD IS NOT NULL

 별도의 WHERE 문이 필요하다면 추가하여 사용해도 무관합니다.

 

 

 

2. MERGE INTO 활용

MERGE INTO EMP_TABLE A
USING INFO_TABLE B
ON (A.EMP_NUMB = B.EMP_NUMB)
WHERE MATCHED THEN
	UPDATE SET
    		A.DEPT_CD = B.DEPT_CD
        	A.DEPT_NM = B.DEPT_NM

  MERGE INTO에서 MATCHED THEN 구문만 사용하여 업데이트할 수도 있습니다. 다만 UPDATE문의 비해 실행속도가 느리므로 굳이 사용하실 필요는 없습니다. INSERT문이 추후에 추가될 여지가 있을 경우에만 유지보수 차원에서 사용할 수 있겠네요.

 

 

 

3. Updatable Join View

UPDATE EMP_INFO A
SET
	(A.DEPT_CD
	, A.PST_CD
	, A.PSPT_CD
	, A.PJW_CD
    ) = (SELECT B.DEPT_CD
        , B.PST_CD
        , B.PSPT_CD
        , B.PJW_CD 
        FROM EMP_TABLE B
        WHERE B.EMP_NUMB = A.EMP_NUMB)
WHERE SHYR = SUBSTR(SF_GET_YMST(SYSDATE), 1, 4)
	AND SMST = SUBSTR(SF_GET_YMST(SYSDATE), 5, 1)
    AND EXISTS (SELECT 1 FROM EMP_INFO 
                WHERE EMP_NUMB = A.EMP_NUMB
                    AND (NVL(DEPT_CD, '0') <> NVL(A.DEPT_CD, '0') OR NVL(PST_CD, '0') <> NVL(A.PST_CD, '0') OR NVL(PSPT_CD, '0') <> NVL(A.PSPT_CD, '0') OR NVL(PJW_CD, '0') <> NVL(A.PJW_CD, '0'))
                )    
;

 간단한 경우에는 1번의 서브쿼리를 이용하는 게 가장 좋습니다. 하지만 위와 같이 쿼리문이 점점 비대해질 수 있습니다. 특히 WHERE절을 걸기 위해 EXISTS로 PK 제약조건을 다시 거는 경우가 많으며, 이는 성능 저하를 일으킵니다.

 

 

UPDATE /*+ bypass_ujvc */
(
	SELECT
		A.DEPT_CD	AS OLD_DEPT_CD
		, A.PST_CD	AS OLD_PST_CD
		, A.PSPT_CD	AS OLD_PSPT_CD
		, A.PJW_CD	AS OLD_PJW_CD
		, B.DEPT_CD	AS NEW_DEPT_CD
		, B.PST_CD	AS NEW_PST_CD
		, B.PSPT_CD	AS NEW_PSPT_CD
		, B.PJW_CD	AS NEW_PJW_CD
	FROM EMP_INFO A, EMP_TABLE B
	WHERE A.EMP_NUMB = B.EMP_NUMB
	AND A.SHYR = SUBSTR(SF_GET_YMST(SYSDATE), 1, 4)
	AND A.SMST = SUBSTR(SF_GET_YMST(SYSDATE), 5, 1)
	AND (NVL(B.DEPT_CD, '0') <> NVL(A.DEPT_CD, '0') OR NVL(B.PST_CD, '0') <> NVL(A.PST_CD, '0') OR NVL(B.PSPT_CD, '0') <> NVL(A.PSPT_CD, '0') OR NVL(B.PJW_CD, '0') <> NVL(A.PJW_CD, '0'))
) A
SET
	A.OLD_DEPT_CD = A.NEW_DEPT_CD
	, A.OLD_PST_CD = A.NEW_PST_CD
	, A.OLD_PSPT_CD = A.NEW_PSPT_CD
	, A.OLD_PJW_CD = A.NEW_PJW_CD

  Updatable Join View는 SET절 대신에 UPDATE 구문에서 뷰 테이블을 만듭니다. 이때 주의할 점은 업데이트할 테이블이 1:1 또는 1:M 구조(업데이트할 테이블이 M)를 가져야 합니다.

 또한 PK를 이용한 조인조건을 사용하거나, Update문 옆에 /*+ bypass_ujvc */ 주석(힌트)을 달아서 해당 뷰가 Updatable Join View임을 알려야 합니다. 일종의 어노테이션(Annotation)이라고 볼 수 있겠네요.