예제에서 사용될 테이블들의 데이터는 아래와 같다.
DEPT 테이블
EMP 테이블
DELETE FROM DEPT D
WHERE EXISTS (SELECT 1
FROM EMP E
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = '10')
-- 에러 발생 // ALIAS 사용으로 인해 에러 발생. 아래와 같이 DELETE 다음에 ALIAS를 써줘보자.
DELETE D FROM DEPT D
WHERE EXISTS (SELECT 1
FROM EMP E
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = '10')
-- (1개 행이 영향을 받음)
UPDATE DEPT D
SET D.LOC = 'VICTORIA'
WHERE EXISTS (SELECT 1
FROM EMP E
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = '20')
-- 에러 발생 // UPDATE 도 마찬가지로 ALIAS 명을 UPDATE 뒤에 적어주자
UPDATE D
SET D.LOC = 'VICTORIA'
FROM DEPT D
WHERE EXISTS (SELECT 1
FROM EMP E
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = '20')
-- (1개 행이 영향을 받음)
MERGE INTO D
USING (SELECT E.DEPTNO
,E.ENAME DNAME
,'TORONTO' LOC
FROM EMP E
WHERE E.DEPTNO = '10' AND E.ENAME = 'KING') DT
ON (D.DEPTNO = DT.DEPTNO AND 'N' = 'Y')
WHEN NOT MATCHED THEN
INSERT
( D.DEPTNO
,D.DNAME
,D.LOC
)
VALUES
(DT.DEPTNO
,DT.DNAME
,DT.LOC
);
-- 에러 발생
MERGE INTO DEPT D
USING (SELECT E.DEPTNO
,E.ENAME DNAME
,'TORONTO' LOC
FROM EMP E
WHERE E.DEPTNO = '10' AND E.ENAME = 'KING') DT
ON (D.DEPTNO = DT.DEPTNO AND 'N' = 'Y')
WHEN NOT MATCHED THEN
INSERT
( DEPTNO
,DNAME
,LOC
)
VALUES
(DT.DEPTNO
,DT.DNAME
,DT.LOC
);
-- (1개 행이 영향을 받음)
