[Database] MERGE INTO문 - Oracle, Tibero

안녕하세요. 오늘은 Oracle과 Tibero DB에서 사용이 가능한 MERGE_INTO문에 대해서 설명해볼까 합니다.

쿼리문을 사용하다보면 INSERT, UPDATE, DELETE 작업을 조건에 맞게 사용해야하는 경우가 있습니다.

예를 들어
값이 있다면 UPDATE, DELETE
값이 없다면 INSERT
쿼리문을 사용하고 싶은 경우

위와 같은 경우에는 Oracle과 Tibero에서 지원하는 MERGE문을 사용하게 되면 해결할 수 있습니다.

1. 기본 MERGE문의 쿼리문

MERGE
 INTO {table_name | View_name} a -- 테이블 또는 뷰
USING {table_name | View_name | Sub Query} b -- 테이블, 뷰, 서브쿼리
 ON (a.column1 = b.column1) --조건절
WHEN MATCHED THEN --일치하는 경우 {UPDATE | DELETE}
	UPDATE SET a.column2 = b.column2
	DELETE
WHEN NOT MATCHED THEN -- 불일치하는 경우 INSERT
	INSERT (a.column1, a.column2, a.column3)
	VALUES (b.column1, b.column2, b.column3)

INTO - INSERT,UPDATE,DELETE 행위를 하고자 하는 테이블명을 넣어줍니다.
USING - INSERT,UPDATE,DELETE 행위를 하는데 있어서 ON의 조건절 사용시 필요한 테이블의 명을 넣어줍니다.
ON - INSERT,UPDATE,DELETE 행위를 하는데 있어 필요한 조건절을 넣어줍니다.
WHEN MATCHED THEN - ON 조건에 맞을 경우 실행되어져야 하는 쿼리문을 넣어줍니다.
WHEN NOT MATCHED THEN - ON 조건에 맞지 않을 경우 실행되어져야 하는 쿼리문을 넣어줍니다.

2. 단일 테이블 사용법(DUAL)

MERGE
 INTO {table_name | View_name} a -- 테이블 또는 뷰
USING dual
 ON (a.column1 = 'a')
WHEN MATCHED THEN --일치하는 경우 {UPDATE | DELETE}
	UPDATE SET a.column2 = 20
WHEN NOT MATCHED THEN -- 불일치하는 경우 INSERT
	INSERT (a.column1, a.column2, a.column3)
	VALUES ('a', 20,'abc')

USING 절에 테이블 대신 dual을 사용한다.
ON 조건절이 일치하면 UPDATE, 불일치하면 INSERT를 하는 쿼리이다.

반응형

3. 조인을 사용하는 방법

MERGE
 INTO table_1 a
USING table_2 b
 ON (a.column1 = 'a' AND a.column1 = b.column1)
WHEN MATCHED THEN --일치하는 경우 {UPDATE | DELETE}
	UPDATE SET 
    a.column2 = b.column2
    a.column3 = b.column3
WHEN NOT MATCHED THEN -- 불일치하는 경우 INSERT
	INSERT (a.column1, a.column2, a.column3)
	VALUES (b.column1, b.column2, b.column3)

 

4. WHERE절 사용

MERGE
 INTO table_1 a
USING dual
 ON (a.column1 = 'a')
WHEN MATCHED THEN
	UPDATE
    	SET a.column2 = 20
    WHERE a.column3 = 'abc'

Oracle 10g 부터는 UPDATE문에서 WHERE절을 사용할 수 있다.
INSERT문에서 WHERE절을 사용하면 오류가 발생한다.

 

5. DELETE절 사용

MERGE
 INTO table_1 a
USING dual
 ON (a.column1 = 'a')
WHEN MATCHED THEN
	UPDATE
    	SET a.column2 = 20
     WHERE a.column3 = 'abc'
    DELETE
     WHERE a.column3 <> 'abc'

Oracle 10g 부터는 DELETE문을 사용할 수 있습니다.
WHERE절을 사용하지 않고 DELETE문만 작성하게 되면 MATCHED된 모든 데이터는 삭제됩니다.

6. 주의사항

MERGE INTO TABLE_NAME A
USING SELECT column1 AS COLUMN1 FROM DUAL) B
ON (A.column1 = B.column1)
WHEN MATCHED THEN
	UPDATE SET
    A.COLUMN2 = A.COLUMN2,
	A.COLUMN2 = A.COLUMN2, 
	A.COLUMN3 = A.COLUMN2 
WHEN NOT MATCHED THEN
	INSERT
	(A.COLUMN1,
	A.COLUMN2,
	A.COLUMN3)
	VALUES
	(B.COLUMN1, 
	B.COLUMN2, 
	B.COLUMN3)

1) ON 조건절에 사용한 컬럼을 업데이트 할 경우 아래와 같은 오류가 발생한다.

'TABLE_NAME' (structure 'Statement'): java.sql.SQLException: ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."COLUMN1"

2) INSERT 조건절에서 INSERT값과 VALUE값의 항목이 다를경우 아래와 같은 오류가 발생한다.

'TABLE_NAME' (structure 'Statement'): java.sql.SQLSyntaxErrorException: ORA-00913: too many values

 

참고 URL

https://gent.tistory.com/406

[Oracle] 오라클 MERGE INTO 사용법 & 노하우 정리