[Oracle] UPDATE, DELETE, MERGE 문 feat by. DML

UPDATE, DELETE, MERGE 문 기본 Syntax

DML 문인 Update, Delete, Merge문은 기본적으로 테이블 데이터를 수정하고, 삭제하고, 병합하는데 사용된다.

-- UPDATE 문
UPDATE 테이블명
SET 컬럼명 = 변경할값 [, 컬럼명1 = 변경할값]
[WHERE 조건식];

-- DELETE 문
DELETE FROM 테이블명
[WHERE 조건신];

-- MERGE 문
MERGE INTO 테이블1 [별칭] -- 합병하는
USING (데이블2 | 뷰 | 서브쿼리) [별칭] -- 합병당하는
ON (조인조건)
WHEN MATCHED THEN -- 조인조건 1 (조인 조건에 만족하는게 있다면...)
  UPDATE SET 
  컬럼명 = 값,
  컬럼명1 = 값1
  [WHERE 조건식]
  [DELETE WHERE 조건식]
  WHEN NOT MATCHED 조건식 -- 조인조건 2
    INSERT ( 컬럼 목록 )
    VALUE ( 값 목록 )
    [WHERE 조건식];

 

Update 문이란?

Update 문은 테이블에 저장된 데이터를 수정하기 위해 사용된다. 하번에 여러 개의 행들을 변경할 수 있다.

 

기본 Syntax는,

UPDATE 테이블명
SET 컬럼명 = 변경할값 [, 컬럼명1 = 변경할값]
[WHERE 조건식];

예로는,

-- SCOTT 계정의 데이터를 사용한 예제

UPDATE mydept
SET dname='영업', loc='경기'
WHERE deptno = 50;

 

서브쿼리를 이용한 UPDATE 문

UPDATE 문의 SET절의 서브쿼리를 사용하면 서브쿼리가 실행된 결과 값으로 테이블을 수정할 수 있다. 이 방법을 사용하면 다른 테이블에 있는 데이터를 사용(참조)하여 특정 컬럼값 변경이 가능해진다.

 

다음은, mydept 테이블의 부서번호가 6-인 사원의 부서명을 dept 테이블의 부서번호가 10인 부서의 부서명으로 수정하고 부서위치는 dept 테이블의 부서번호가 20인 부서위치로 수정하는 SQL 문이다. 

UPDATE mydept
SET dname = (SELECT dname
             FROM dept
             WHERE deptno = 10)
   ,loc = (SELECT loc
           FROM dept
           WHERE deptno = 20)
WHERE deptno = 60;

 

DELETE 문이란?

DELETE 문은 테이블에 저장된 데이터를 삭제하기 위해서 사용된다. 한 번에 여러 개의 행들을 삭제할 수 있다. 

 

기본 Syntax는,

DELETE FROM 테이블명
[WHERE 조건신];

 

서브쿼리를 이용한 DELETE 문

DELETE 문의 WHERE 절에서 서브쿼리를 사용하면 서브쿼리가 실행된 결과값으로 테이블의 데이터를 삭제 할 수 있다. 이 방법을 사용하면 다른 테이블에 저장된 데이터를 사용하여 특정 데이터를 삭제 할 수 있다. 

 

다음은 SCOTT 계정을 사용하여, Dept 테이블에서 부서번호가 20인 부서 위치와 동일한 위치에 해당하는 행을 mydept 테이블에서 삭제하는 SQL 문이다.

DELETE
FROM mydept
WHERE loc = (SELECT loc
             FROM dept
             WHERE deptno = 20);

 

MERGE 문이란? aka 햄버거 조인

MERGE 문은 구조가 같은 2개의 테이블을 비교하여 하나로 합티기 위한 데이터 조작어이다. MERGE 문이 사용되는 대표적인 예로는 전자상거래와 같은 물품을 판매하는 회사에서 관리는 월별 판매 실적 데이터를 하나의 연간 테이블로 만들때 사용한다. 

 

JOIN 에서 테이블을 컬럼간 컬럼식인 가로로 붙였다면, MERGE 에서는 세로로 붙인다고 이해하면된다. 

 

기본 Syntax는, 

MERGE INTO 테이블1 [별칭] -- 합병하는
USING (데이블2 | 뷰 | 서브쿼리) [별칭] -- 합병당하는
ON (조인조건)
WHEN MATCHED THEN -- 조인조건 1 (조인 조건에 만족하는게 있다면...)
  UPDATE SET 
  컬럼명 = 값,
  컬럼명1 = 값1
  [WHERE 조건식]
  [DELETE WHERE 조건식]
  WHEN NOT MATCHED 조건식 -- 조인조건 2
    INSERT ( 컬럼 목록 )
    VALUE ( 값 목록 )
    [WHERE 조건식];

 

MERGE 문 예제,

MERGE 문 실습을 위한 테이블 생성. 

CREATE TABLE pt_01
( 판매번호 VARCHAR2(8),
  제품번호 NUMBER,
  수량    NUMBER,
  금액    NUMBER);
  
CREATE TABLE pt_02
( 판매번호 VARCHAR2(8),
  제품번호 NUMBER,
  수량    NUMBER,
  금액    NUMBER);
  
CREATE TABLE p_total
( 판매번호 VARCHAR2(8),
  제품번호 NUMBER,
  수량    NUMBER,
  금액    NUMBER);
  
INSERT INTO pt_01 VALUES ( '20170101', 1000, 10, 500);
INSERT INTO pt_01 VALUES ( '20170102', 1001, 10, 400);
INSERT INTO pt_01 VALUES ( '20170103', 1002, 10, 300);

INSERT INTO pt_02 VALUES ( '20170101', 1003, 5, 500);
INSERT INTO pt_02 VALUES ( '20170102', 1004, 5, 400);
INSERT INTO pt_02 VALUES ( '20170103', 1005, 5, 300);

다음은 1월 판매정보를 가진 pt_01 테이블과 통합 테이블인 pt_total을 병합하여 하는 쿼리이다. p_total과 pt_01 테이블간에 일치하는 데이터가 하나도 없기 때문에 INSERT 문이 실행되어 p_total 테이블에 pt_01 테이블의 데이터가 저장된다.

MERGE INTO p_total total
USING pt_01 p01
ON ( total.판매번호 = p01.판매번호)
WHEN MATCHED THEN
  UPDATE SET total.제품번호 = p01.제품번호
WHEN NOT MATCHED THEN
  INSERT VALUES ( p01.판매번호, p01.제품번호, p01.수량, p01.금액);

이 쿼리를 쪼개서 추상화 하면,

 

- INTO 절에는 병합된 데이터를 저장하기 위한 통합 테이블인 p_total를 지정하고 별칭으로 total를 사용한다.

- USING 절에는 병합할 1월 판매정보가 저장된 pt_01를 지정하고 별칭으로 p01을 사용한다. 

- ON 절에는 p_total 테이블과 조인 조건을 지정한다.

- 만약 조인조건이 일치하면 UPDATE문이 실행되고 일치하지 않으면 INSERT 문이 실행된다