Q. 다른 값을 사용하여 테이블의 행을 업데이트하려고 한다

예를 들어, 특정 사원의 새로운 급여가 저장된 'NEW_SAL'이라는 테이블이 있다.
'NEW_SAL'의 내용은 다음과 같다.

/* Oracle */
create table new_sal(
	deptno number not null,
	sal number,
	constraint fk_new_sal_deptno foreign key (deptno) references dept (deptno)
	);
    
/* MAIRA */
create table new_sal(
	deptno int not null,
	sal int,
	constraint fk_new_sal_deptno foreign key (deptno) references dept (deptno)
	);
   
/* COMMON */
insert into new_sal (deptno, sal) values
(
	10, 4000
);
>>
DEPTNO	SAL
10		4000

'DEPTNO' 열을 'NEW_SAL' 테이블의 기본 키(PRIMARY KEY)로 잡아주라고 한다.

/* Oracle */
alter table new_sal
add constraint pk_new_sal_deptno primary key (deptno);
>>Table altered.

/* MARIA */
alter table new_sal
add constraint pk_new_sal_deptno primary key (deptno);
>>
Query OK, 0 rows affected, 1 warning (0.246 sec)
Records: 0  Duplicates: 0  Warnings: 1

show warnings;
>>
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1280 | Name 'pk_new_sal_deptno' ignored for PRIMARY key. |
+---------+------+---------------------------------------------------+

MARIA에서 PK 제약조건에 이름을 붙일 수 없다.
그래서 '제약조건명을 붙이는 건 무시하였다'('그런 거 우린 안 해요')라 한다.
PK 설정은 정상적으로 이루어진다.

세부 목표

1. EMP.DEPTNO와 NEW_SAL.DEPTNO의 값이 일치할 경우, NEW_SAL 테이블을 사용하여 EMP 테이블에서 특정 사원(해당하는 사원)의 급여 및 커미션을 업데이트 한다
2. EMP.SAL을 NEW_SAL.SAL로 업데이트한 후 EMP.COMM을 NEW_SAL.SAL의 50%로 업데이트 한다

A. 'NEW_SAL'과 'EMP' 사이에 JOIN을 사용하고 새 COMM값을 찾아 UPDATE 문으로 반환한다.

이와 같은 업데이트는 일반적으로 상관 서브쿼리 또는 CTE를 사용하여 수행된다.
또 다른 기법은 뷰 또는 인라인 뷰를 생성한 다음, 해당 뷰를 업데이트하는 것이다.

/* MARIA */
update emp e, new_sal ns
	set e.sal 	= ns.sal,
    	e.comm  = ns.sal/2
        where e.deptno = ns.deptno;
>>
Query OK, 4 rows affected (0.003 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MARIA에서의 UPDATE문은 굉장히 간결하고 알기 쉽다.
UPDATE문에 직접적으로 JOIN을 사용할 수 있다.

그러나 이 쿼리는 Oracle에서는 작동하지 않는다.

Oracle에서는 다음과 같은 쿼리를 사용한다.

/* Oracle */
update(
select e.sal as emp_sal, e.comm as emp_comm,
        ns.sal as ns_sal, ns.sal/2 as ns_comm
        from emp e, new_sal ns
        where e.deptno = ns.deptno
        )
        set emp_sal = ns_sal, emp_comm = ns_comm

>>
Statement processed.

인라인 뷰를 생성하고 이 뷰에 대한 업데이트를 실행하는 쿼리를 사용한다.
이러한 형태의 업데이트에 있어 '키 보존'을 알아야 한다.

• 'NEW_SAL' 테이블의 'DETPNO'열은 고유하다(PK)
• 그러나, 위의 인라인 뷰에서처럼 JOIN이 행해질 때의 결과셋에서는 고유하지 않다

/* 위의 인라인 뷰와 동일한 유형의 쿼리임 */
select e.empno, e.deptno e_deptno, ns.sal, ns.deptno ns_deptno
	from emp e, new_sal ns
    where e.deptno = ns.deptno;
>>
EMPNO	E_DEPTNO	SAL		NS_DEPTNO
7782	10			4000	10
7934	10			4000	10
7839	10			4000	10
3 rows selected.

결과셋에서는 NS_DEPTNO('NEW_SAL'테이블의 'DEPTNO')가 고유하지 않음을 알 수 있다.

인라인 뷰를 생성하고, 이에 대한 업데이트를 실행하는 쿼리에서
해당 값이 결과셋에서 고유하지 않은 경우라도 해당 값이 원본 테이블에서는 고유해야 한다.
'NS_DEPTNO'가 결과셋에서는 중복되어 나왔지만, 'NEW_SAL' 테이블에서는 무조건 고유해야 한다.

그래서 처음 테이블 생성 때 PK로 만들어준 것이다.

이런 설정을 해두면, 결과셋에서 해당 값이 고유하지 않다고 해도 키가 보존된 것으로 간주되고 업데이트가 정상적으로 행해진다.

---

정리

• UPDATE에서 직접 JOIN이 가능한 MARIA의 쿼리가 굉장히 편해보인다
• 쿼리는 길어지지만 명확성 측면에서는 Oracle도 좋아보인다
• 상관 서브쿼리가 뭔지는 알겠다
  이걸 의도적으로 어떻게 사용할지에 대해선 멀뚱멀뚱

상관 서브쿼리 다시 확인

• 보통 일반적인 서브쿼리는 딱 한 번, 서브쿼리가 먼저 실행되고 그 결과셋을 외부쿼리가 전달받아 실행된다
  그리고 이런 쿼리를 외부쿼리와 서브쿼리로 나눴을 때, 단독으로 실행이 가능하다
• 그러나, 상관 서브쿼리는 외부쿼리와 서브쿼리로 나눴을 때 단독으로 실행할 수 없다
  서로의 결과셋이 서로의 쿼리 실행에 영향을 주기 때문이다

상관 서브쿼리: 읽은 글
1. SQL 상관 서브쿼리: muntari Log
2. 상관관계 서브쿼리: Java 맛 코드의 향기