SQL_DML(데이터조작어)_UPDATE, DELETE, MERGE(수정,삭제,병합)

📖 데이터 수정 (UPDATE)

1. 개요

• update 명령문은 테이블에 저장된 데이터 수정을 위한 조작어이다.
• where 절을 생략하면 테이블의 모든 행을 수정한다.

2. 기본 형식

UPDATE 테이블
SET 컬럼 = 수정데이터,
컬럼2 = 수정데이터2,
..
WHERE 조건문

UPDATE EMP_CP100 
SET ename = '이현규', job = '대리'
WHERE empno = 7782; 

-- ex) 부서번호가 10인 사원정보의 입사일을 오늘로 변경하고, sal을 10%인상금액으로 처리.
UPDATE EMP_CP100
SET hiredate = sysdate, sal = sal*1.1
WHERE deptno = 10;

📌 subquery를 통한 수정 처리

• update명령문에 set부분이나 where조건절에 subquery를 이용하여 수정처리하는 것을 말한다.
• 다른 테이블이나 현재 테이블에 정보를 변경할 때, 일단 query를 수행한 결과로 해당 데이터를 수정 처리하는 것을 말한다.
• 변경할 컬럼의 타입과 갯수는 반드시 일치하여야 한다.
• 유형

  • set 부분 subquery 활용
    한개의 컬럼 : set 컬럼명 = (결과가 1개인 컬럼데이터)
    두개 이상의 컬럼 : set (컬럼명1, 컬럼명2, ...) = (결과가 2개인 컬럼데이터)

-- 부서번호가 10인 사원정보의 급여를 부서번호 20인 사원의 최고 급여로 변경 처리
--테이블 복사해서 생성
CREATE TABLE EMP_CP101 AS SELECT * FROM emp;
-- 수정
UPDATE EMP_CP101
 SET sal = (
 SELECT max(sal)
 FROM EMP_CP101
 WHERE deptno = 20
 )
WHERE deptno = 10; 

-- ex) 직책이 SALESMAN인 사원 평균급여를 사원번호 7499에 급여로 수정처리하세요. 
UPDATE EMP_CP101 
SET sal = (
 SELECT AVG(SAL) 
 FROM EMP_CP101 
 WHERE JOB = 'SALESMAN'
 )
WHERE empno = 7499;

-- 두개 열에 대한 subquery 처리
-- 부서별 최고 급여 중에 최고 급여가 3000 미만인 부서 번호와 급여를 사원번호 7369로 변경 
처리
UPDATE EMP_CP101
 SET (deptno,sal) = (
 SELECT deptno, max(sal)
 FROM EMP_CP101
 GROUP BY deptno
 HAVING max(sal)<3000
)
WHERE empno = 7369;

-- ex) 직책별로 최고 급여자 중에 SALESMAN의 직책과 급여를 사원번호 7782로 변경 처리
UPDATE EMP_CP101
SET (job, sal) = (
 SELECT JOB, MAX(SAL)
 FROM EMP_CP101
 WHERE job = 'SALESMAN'
 GROUP BY JOB
)
WHERE empno = 7782;

📖 데이터의 삭제 (DELETE)

• delete 명령문은 테이블에 저장된 데이터 삭제를 위한 조작어이다.
• where 절을 생략하면 테이블의 모든 행이 삭제 된다.
• 기본 형식

  DELETE
  FROM 테이블명
  WHERE 조건

DELETE FROM EMP_CP100 ec
WHERE empno = 7782;

--테이블 복사해서 생성
CREATE TABLE EMP01
AS SELECT * FROM emp; 

-- ex) 급여가 2000 이상이고 부서가 30인 데이터를 삭제처리하세요. 
DELETE
FROM EMP01 
WHERE sal >=2000 AND deptno = 30;

📌 서브쿼리를 이용한 데이터 삭제

• where절에서 서브쿼리 이용
• 다른 테이블에 저장된 데이터를 검색하여 한꺼번에 여러 행의 내용을 삭제함.
• where 절의 컬럼 이름은 서브 쿼리의 컬럼 이름과 달라도 됨
• 데이터 타입과 컬럼 수는 일치
• 기본 형식

1) row인 경우
DELETE FROM 테이블
WHERE (컬럼명1, 컬럼명2..._ = (select ... from 테이블 where)

2) 다중의 row인 경우
DELETE FROM 테이블
WHERE(컬럼명1, 컬럼명2...) in (select .. from 테이블)
cf) 다중 컬럼인 경우에 해당 컬럼 데이터가 1개 row에 같이 있을 때, 삭제 된다.

-- 부서별 최고 급여자 삭제
DELETE FROM emp01 
WHERE (deptno, sal) in ( 
 SELECT deptno, max(sal) sal 
 FROM EMP01
 GROUP BY deptno
);

-- ex) 분기별 최저 급여자를 삭제하세요. 
DELETE FROM EMP01 
WHERE (to_char(hiredate, 'Q'), sal) IN (
 SELECT to_char(hiredate, 'Q') div, min(sal) sal 
 FROM emp01 
 GROUP BY to_char(hiredate, 'Q')
 );


-- 과제) emp02로 복사테이블을 만들고, delete subquery를 이용하여, 상/하반기 최고 급여자를 
삭제처리하세요.
-- 테이블 생성
CREATE TABLE emp02
AS SELECT * FROM emp;

-- 상반기 삭제
DELETE FROM emp02
WHERE (to_char(hiredate, 'Q'), sal) IN (
 SELECT to_char(hiredate, 'Q') div, max(sal) sal 
 FROM EMP02
 WHERE (hiredate, 'Q') <= 2;
 GROUP BY to_char(hiredate, 'Q')
);
-- 하반기 삭제
DELETE FROM emp02
WHERE (to_char(hiredate, 'Q'), sal) IN (
 SELECT to_char(hiredate, 'Q') div, max(sal) sal 
 FROM EMP02
 WHERE to_char(hiredate, 'Q') >= 3
 GROUP BY to_char(hiredate, 'Q')
);

📖 데이터 병합 (MERGE)

1. 개념

• 구조가 같은 두 개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작어
• where 절의 조건절에서 결과 테이블에 해당 행이 존재하면 update명령문에 의해 새로운
  값으로 수정
  그렇지 않으면 insert 명령문으로 새로운 행을 삽입
• 대량의 데이터를 분석하기 위한 업무에 유용
• 전자상거래에서 상품정보를 등록하는데, 해당 상품이 등록되어 있으면 update로 상품정보가
  수정이 되고, 상품이 등록되어 있지 않으면 상품정보를 수정처리되게 하는 경우에 사용된다.

2. 기본형식

merge into 목적테이블명
using 비교할 테이블 및 subquery
on 두 테이블의 비교 조건
when matched then 비교조건이 일치할 때, 수정 구문 처리
when not matched then 비교조건이 일치하지 않을 떄, 등록 구문 처리

-- 테이블 복사 생성
CREATE TABLE emp03 
AS SELECT * FROM emp;
SELECT * FROM emp03; 

-- 사원번호 기준으로 해당 데이터가 있으면 수정 처리, 데이터가 없으면 등록 처리
-- # merge를 쓰지 않을 때
-- 1) 임시테이블을 만든다. - 등록할 정보를 설정
CREATE TABLE EMP04
AS SELECT * 
FROM emp 
WHERE deptno = 10;

-- 2) 수정 처리
UPDATE EMP03 
SET ename = '김길동' 
WHERE empno = 7782; 
 
-- 3) 등록 처리
INSERT INTO emp03 VALUES(8001, '이길동', '사원', 7782, sysdate, 3000, NULL, 20); 

-- # merge를 사용할 때
-- 1) 임시테이블 만들기
-- 2) merge를 통한 조건 처리
-- 3) 조건이 true일 때, 처리할 내용
-- 4) 조건이 false일 때, 처리할 내용
SELECT * FROM emp04; -- 입력 또는 수정할 정보가 있는 테이블

MERGE INTO emp03 t 
USING emp04 s
ON(t.empno = s.empno)
WHEN MATCHED THEN -- empno가 있을 때 
	UPDATE SET t.ename = s.ename
WHEN NOT MATCHED THEN -- empno가 없을 때
	INSERT VALUES(s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, 
s.deptno);

 -- ex1) emp05와 emp06을 머지처리
CREATE TABLE emp05 -- 기능테이블 (입력할 테이블)
AS SELECT * 
FROM emp 
WHERE deptno=20;

CREATE TABLE emp06 -- 목적테이블 (등록/수정할 테이블)
AS SELECT * 
FROM emp;

MERGE INTO emp05 p 
USING emp06 m
ON(p.empno = m.empno)
WHEN MATCHED THEN
   UPDATE SET p.ename = m.ename
WHEN NOT MATCHED THEN
   INSERT VALUES(m.empno, m.ename, m.job, m.mgr, m.hiredate, m.sal, m.comm, 
m.deptno); 

-- 입력하는 데이터에는 사원번호가 있을 때는 수정이 되고, 사원번호가 없을 때는 등록 처리
-- 1. 목적테이블 선언
-- 2. 입력할 가상 테이블 dual 활용 입력/수정 내용 설정
-- 3. merge into 처리
CREATE TABLE emp07
AS SELECT * FROM emp;

-- empno가 있는 경우
SELECT 7369 empno, '홍길동' ename, '사원' job, 7902 mgr, sysdate hiredate, 2000 sal, 
100 comm, 10 deptno 
FROM dual; 

-- empno가 없는 경우
SELECT 8001 empno, '오영심' ename, '대리' job, 7902 mgr, sysdate hiredate, 4000 sal, 
100 comm, 10 deptbo 
FROM dual;

MERGE INTO emp07 t 
USING (SELECT 7369 empno, '홍길동' ename, '사원' job, 7902 mgr, sysdate hiredate, 
2000 sal, 100 comm, 10 deptno FROM dual) s
ON (t.empno = s.empno)
WHEN MATCHED THEN 
 UPDATE SET t.ename = s.ename,
 			t.job = s.job,
			t.mgr = s.mgr,
			t.hiredate = s.hiredate,
            t.sal = s.sal,
            t.comm = s.comm,
            t.deptno = s.deptno
WHEN NOT MATCHED THEN 
 INSERT values(s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno);
 

MERGE INTO emp07 t 
USING (SELECT 8001 empno, '오영심' ename, '대리' job, 7902 mgr, sysdate hiredate, 
4000 sal, 100 comm, 10 deptno FROM dual) s
ON (t.empno = s.empno)
WHEN MATCHED THEN 
 UPDATE SET t.ename = s.ename,
   			t.job = s.job,
            t.mgr = s.mgr,
            t.hiredate = s.hiredate,
            t.sal = s.sal,
            t.comm = s.comm,
            t.deptno = s.deptno
WHEN NOT MATCHED THEN 
 INSERT values(s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno);