다른 테이블의 SELECT 한 결과물을 참조하여 현재 테이블을 UPDATE 해야 하는 상황이 있습니다.
Oracle, MS-SQL, Maria DB, Mysql DBMS마다 다른 문법을 지원하므로 방법도 각자 다릅니다.
저는 Oracle을 사용 중인데 다른 DBMS 보다 조금 문법이 어려운 것 같네요 이 방법에 한해서는요 ㅎㅎ
table master, table user 두 개의 테이블이 있다고 가정하겠습니다.
master 테이블의 Status을 user 테이블의 Status의 값을 참조하여 변경하는 쿼리를 작성해보겠습니다.
MS-SQL
UPDATE
master
SET
master.status = cust.status
FROM
master m
INNER JOIN
cust c
ON
m.id = c.id;MySQL and MariaDB
UPDATE
master m,
cust c
SET
m.status = c.status
WHERE
m.id = c.id;Oracle
단순 업데이트만 사용하는 방법
EXISTS를 사용하지 않으면 첫 번째 서브 쿼리의 id 키 값이 매칭 되지 않는 값들은 master의 status 값이 null로 변경됩니다.
UPDATE master m
SET m.status = (select c.status
from cust c
where m.id = c.id)
WHERE EXISTS (select 1
from cust c
where m.id = c.id)merge into를 구문을 사용하는 방법
MERGE INTO master m
USING cust c
ON (m.id = c.id)
WHEN MATCHED THEN UPDATE SET m.status = c.status;Oracle 실습
이 쿼리를 실행해보겠습니다.
UPDATE master m
SET m.status = (select c.status
from cust c
where m.id = c.id)
WHERE EXISTS (select 1
from cust c
where m.id = c.id)우리가 기대하는 결과는 master 테이블의 test1, test3, test5의 status값이 FALSE로 변환되는 결과입니다.
올바르게 변환됩니다!
그런데 만약 아래와 같이 EXIST 구문을 빼면 어떻게 될까요?
UPDATE master m
SET m.status = (select c.status
from cust c
where m.id = c.id)
test2와 test4는 id 키 값이 매칭되는 것이 없으므로 status의 값이 null로 변경됩니다. 기본적으로 update 구문에서 where 조건을 설정하지 않으면 전체 테이블을 update하게 되어서 나타나는 결과입니다.
merger into 쿼리의 결과 역시 제대로 업데이트 되는 것을 볼 수 있습니다!
MERGE INTO master m
USING cust c
ON (m.id = c.id)
WHEN MATCHED THEN UPDATE SET m.status = c.status;
오라클에서 테이블의 값을 수정하기 위해서는 UPDATE 문을 사용하면 된다. WHERE 절에 해당하는 행이 여러 개인 경우 한 번에 여러 건의 행이 SET 절의 값으로 변경된다. UPDATE 문은 시스템 운영 시 자주 사용하므로 다양한 방법을 익혀두는 것이 좋다.
오라클 UPDATE 문
기본 사용법
UPDATE emp
SET job = 'MANAGER'
, deptno = 20
WHERE empno = 7566
위는 WHERE 절의 empno에 해당하는 데이터의 job, deptno 컬럼을 수정하는 쿼리이다. empno는 PK이기 때문에 한건의 데이터만 수정되지만 PK가 아니고 여러 건이 조회되면 여러 건의 데이터가 한 번에 수정된다.
SET 절에 서브쿼리 사용법
UPDATE emp a
SET a.sal = (SELECT MAX(aa.sal) FROM emp aa WHERE aa.job = a.job)
, a.comm = (SELECT MAX(aa.comm) FROM emp aa WHERE aa.job = a.job)
WHERE a.job = 'ANALYST'
다른 테이블에서 데이터를 조회하여 UPDATE를 해야 하는 경우가 있다. 조인을 사용하면 편리할 거 같지만 UPDATE문에서 조인은 제약사항이 많기 때문에 서브 쿼리를 활용하면 쉽게 해결되는 경우가 많다.
UPDATE emp a
SET (a.sal, a.comm) = (SELECT MAX(aa.sal), MAX(aa.comm)
FROM emp aa
WHERE aa.job = a.job)
WHERE a.job = 'ANALYST'
동일한 서브쿼리를 반복해서 사용해야 하는 경우 컬럼을 묶어서 한 번의 서브 쿼리로 업데이트 값을 입력할 수 있다.
WHERE 절에 IN 사용법
UPDATE emp
SET sal = 3000
WHERE empno IN (SELECT aa.empno
FROM emp aa
, dept bb
WHERE aa.job = 'ANALYST'
and aa.deptno = bb.deptno)
업데이트 범위를 정할 때 다른 테이블을 참조해야 할 경우 IN을 사용하여 데이터를 포함시키거나 NOT IN을 사용하여 제외시킬 수 있다.
UPDATE emp
SET comm = 500
WHERE (job, deptno) IN (SELECT aa.job, aa.deptno
FROM emp aa
WHERE aa.deptno = 30)
IN의 서브 쿼리에서 키값이 여러 개인 경우 컬럼을 묶어서 조건을 맵핑할 수 있다.
UPDATE emp a
SET a.comm = 500
WHERE a.ROWID IN (SELECT aa.ROWID
FROM emp aa
WHERE aa.deptno = 30)
동일한 데이블을 사용한다면 ROWID를 키로 사용할 수 있다.
WHERE 절에 EXISTS 사용법
UPDATE emp a
SET a.comm = 500
WHERE EXISTS (SELECT 1
FROM emp aa
WHERE aa.empno = a.empno
AND aa.deptno = 30)
EXISTS를 사용하면 쉽게 데이터를 제외시키거나 포함시킬 수 있다. IN과 비슷한 역할을 하지만 성능면에서 조금 더 나을 수 있다.
UPDATE emp a
SET a.comm = 500
WHERE NOT EXISTS (SELECT 1
FROM emp aa
WHERE aa.empno = a.empno
AND aa.deptno = 30)
조인하여 UPDATE 하는 방법
UPDATE (SELECT a.sal
, a.comm
, b.sal AS sal_2
, b.comm AS comm_2
FROM emp a
, emp_man b
WHERE a.empno = b.empno)
SET sal = sal_2
, comm = comm_2
두 개의 테이블을 조인해서 업데이트해야 할 경우 사용하는 방법이다. 위의 쿼리는 오라클 11.2 이하의 버전에서는 오류가 발생하며 아래의 MERGE 문을 사용해야 한다.
MERGE
INTO emp a
USING emp_man b
ON (a.empno = b.empno)
WHEN MATCHED THEN
UPDATE
SET a.sal = b.sal
, a.comm = b.comm;
MERGE 문은 오라클 9i부터 사용할 수 있다. BYPASS_UJVC 힌트를 사용하는 방법이 있으나 11g부터 공식적으로 사용을 중단하였기 때문에 특별한 경우가 아니면 MERGE 문을 사용할 것을 권장한다.
MERGE INTO를 하는 중 다음과 같은 오류 메세지가 발생하였다.
ORA-30926 : 원본 테이블의 고정 행 집합을 가져올 수 없습니다.
ORA-30926 : unable to get a stable set of rows in the source tables
MERGE INTO 구문
====================================================================
MERGE INTO table_name alias -- (실제 데이터를 INSERT 또는 UPDATE할 테이블)
USING (table | view | subquery) alias
-- 실제 데이터를 조회할 대상 테이블(뷰, 서브쿼리)
-- 대상 테이블이 없는 경우 DUAL 테이블 사용
ON (join condition)
-- 조건절 (Where절) 이 조건에 의해 아래 MATCHED / NOT MATCHED 로 분기
WHEN MATCHED THEN -- ON 조건에 해당하는 데이터(레코드)가 존재한다면
UPDATE SET [column1] = [value1] -- 해당 레코드를 대상으로 UPDATE 실행
WHEN NOT MATCHED THEN -- ON 이하의 조건에 해당하는 데이터(레코드)가 존재하지 않는다면
INSERT (column1, column2 ...) VALUES (value1, value2 ...);
-- 새 데이터를 추가해야 하므로 INSERT 실행
====================================================================
MERGE INTO 구문 중 INSERT와 UPDATE는 하나의 레코드를 대상으로 작업을 수행할 수 있는데, 2개 이상의 SELECT 결과가 나와서 발생하는 오류였다.
USING ( ) ON ( ) 조건에 의해 SELECT 된 결과가 2건이상 발생하는 경우 동일한 레코드가 중복 삽입되는 될 수 있으며, 이때 PK 무결성 오류가 발생하기도 한다.
또는 update되는 로우가 1건 이상이 발생한다.
정리하자면
1) INTO 절에 사용되는 테이블에 Primary Key 를 사용하는 경우
- 즉 INSERT 구문에서 DUPLICATE가 발생하거나 UPDATE 에 MULTI ROW가 UPDATE되는 경우
2) ON 구문에서 UPDATE되는 ROW가 1개 이상일 경우
- 즉 ON 구문에서 맞는 테이블 값이 하나 이상일 경우
그렇기 때문에 USING ( ) 조건에 의해 산출되는 SELECT 결과가 중복 레코드를 갖지 않도록 수정하면 해결된다.
ORACLE 12 이상
MERGE INTO SPEC_MANUF m
USING COMM_CD c
ON (m.CORP_CD = c.SCODE AND c.MCODE='11' )
WHEN MATCHED THEN UPDATE SET m.CORP_NATION = c.CALL_NAME;
SELECT * FROM SPEC_MANUF;
ORACLE 12 이하
UPDATE SPEC_MANUF a
SET a.CORP_NATION = (SELECT aa.CALL_NAME
FROM COMM_CD aa
WHERE aa.SCODE = a.CORP_CD
AND aa.MCODE = '11')
WHERE EXISTS (SELECT aa.SCODE
FROM COMM_CD aa
WHERE aa.SCODE = a.CORP_CD
AND aa.MCODE = '11'
);