: 조건에 의해 한꺼번에 INSERT, UPDATE, DELETE 할 수 있는 명령문 ON절에 있는 조건에 UPDATE할 컬럼이 들어가면 안 됨. 오류 발생
MERGE INTO EMP USING DEPT ON (EMP.DEPTNO = DEPT.DEPTNO)
WHEN MATCHED THEN UPDATE SET EMP.DNAME = DEPT.DNAME DELETE WHERE EMP.MGR IS NULL
WHEN NOT MATCHED THEN INSERT (EMPNO, ENAME) VALUES (9999,'TEST');
SYNTAX MERGE INTO변경을원하는테이블명1 [별칭] --서브쿼리가 와도 된다. USING데이터를가져올테이블명2 [별칭] --뷰(인라인뷰, 이너뷰), 테이블, 서브쿼리 등 가능 ON(테이블1과 테이블2를 엮을 조건)
[WHEN MATCHED THEN -- 이 절에는 반드시 UPDATE문이 와야 한다. UPDATE SET테이블1의컬럼1 = 테이블2의컬럼1, 테이블1의컬럼2 = 테이블2의컬럼2, ... [DELETE WHERE조건 **반드시UPDATE절과 같이써야 한다.] ]
[WHEN NOT MATCHED THEN -- 이 절에는INSERT문이 올 수 있다. INSERT(테이블1의컬럼1, 테이블1의컬럼2, ...) VALUES (테이블2의컬럼1, 테이블2의컬럼2, ...);]
--WHEN, WHEN NOT간 순서 상관x - DML
ex)
MERGE INTO (SELECT * FROM EMP WHERE DEPTNO = 30) E
USING (SELECT DEPARTMENT_NAME, DEPTNO FROM DEPT DD, DEPARTMENTS S WHERE DD.DEPTNO = S.DEPARTMENT_ID) D
ON (E.DEPTNO = D.DEPTNO)
WHEN MATCHED THEN
UPDATE SET E.DNAME = D.DEPARTMENT_NAME;
ex)
--DEPT=20인 경우에만 DNAME 갖고오기
MERGE INTO (SELECT * FROM EMP WHERE DEPTNO = 20) E
USING DEPT D
ON (E.DEPTNO = D.DEPTNO)
WHEN MATCHED THEN
UPDATE SET E.DNAME = D.DNAME
DELETE WHERE E.MGR IS NULL;
ex)
CREATE TABLE EMP01
AS
SELECT * FROM EMP WHERE DEPTNO = 10;
MERGE INTO EMP01 A
USING (SELECT EMPNO, SAL, JOB FROM EMP WHERE DEPTNO=10) B
ON (A.EMPNO = B.EMPNO)
WHEN MATCHED THEN
UPDATE SET A.SAL = SAL*1.01
WHEN NOT MATCHED THEN
INSERT (A.EMPNO, SAL, JOB) VALUES (B.EMPNO, B.SAL, B.JOB);
ex)
MERGE INTO EMP01 A
USING (SELECT EMPNO, SAL, JOB FROM EMP WHERE DEPTNO=20) B
ON (A.EMPNO = B.EMPNO)
WHEN NOT MATCHED THEN
INSERT (A.EMPNO, SAL, JOB) VALUES (B.EMPNO, B.SAL, B.JOB)
WHEN MATCHED THEN
UPDATE SET A.SAL = SAL*1.01;
ex)
MERGE INTO EMP01 A
USING (SELECT EMPNO, SAL, JOB FROM EMP WHERE ENAME = 'KING' AND DEPTNO = 10) B
ON (A.EMPNO = B.EMPNO)
WHEN MATCHED THEN
UPDATE SET A.SAL = 8000
WHEN NOT MATCHED THEN
INSERT (A.EMPNO, SAL, JOB) VALUES (B.EMPNO, B.SAL, B.JOB);
MERGE INTO EMP01 A
USING (SELECT EMPNO, SAL, JOB FROM EMP WHERE ENAME = 'KING' AND DEPTNO = 10) B
ON (A.EMPNO = B.EMPNO)
WHEN MATCHED THEN
UPDATE SET A.SAL = 8000
DELETE WHERE A.DEPTNO = 10 --한줄에 해당되는 조건에 걸린것만 적용됨.
WHEN NOT MATCHED THEN
INSERT (A.EMPNO, SAL, JOB) VALUES (B.EMPNO, B.SAL, B.JOB);
