서브쿼리에 대해 알아보자 5편 (feat. MERGE, UPDATE FROM)

안녕하세요, 끙정입니다.

 

오늘은 서브 쿼리의 마지막 편인 테이블 병합입니다.

MERGE와 UPDATE FROM이 있습니다.

천천히 알아보겠습니다.

 

1. MERGE(Oracle, SQL Server Only)

작업을 할 때는 혹시 모를 사고를 대비하기 위해 사본을 떠 두거나 백업을 떠 두곤 합니다.

그리고 변경된 사본 데이터를 다시 원본으로 가져오는데,

정확하게 똑같이 만들어야 한다면 덮어써 버리 되지만 때로는 선별적으로 가져와야 할 때도 있습니다.

이럴 때 무엇이 바뀌었는지 일일이 살펴보고 바뀐 건 UPDATE 하고 지운 건 다시 INSERT 해야 합니다.

이런 작업을 한 번에 해주는 것이 바로 MERGE입니다.

비교, 삽입, 갱신을 한 방에 해준다고 해서 UPSERT라고 부르기도 합니다.

 

다만 두 테이블에 대해 여러 가지 연산을 수행하는 데다 조건과 각 경우의 동작이 달라 기본 형식이 복잡합니다.

문법은 아래와 같습니다.

 

MERGE INTO 타겟 USING 소스 ON (일치조건)
WHEN MATCHED THEN
    UPDATE ...
    DELETE ...
WHEN NOT MATCHED THEN
    INSERT ...

 

INTO 다음에 타깃, USING 다음에 소스 테이블을 지정합니다.

테이블 대신 뷰를 지정할 수 있고 소스는 테이블을 생성하는 서브 쿼리도 가능합니다.

ON 절에는 두 테이블을 비교할 조건을 지정합니다.

두 테이블의 레코드를 순서대로 비교하여 일치하면 MATCHED의 명령을 실행하고,

그렇지 않으면 NOT MATCHED의 명령을 실행합니다.

 

조건이 일치하면 같은 레코드라는 뜻이므로 UPDATE 문으로 필요한 필드를 복사하여 동기화합니다.

또한 DELETE문으로 소스에 아직 남아 있는 레코드를 타깃에서 지울 수도 있습니다.

조건이 일치하지 않으면 소스에만 있고 타깃에는 없었던 레코드이므로 새로 삽입하여 동기화합니다.

 

아래의 두 테이블로 예를 들어보겠습니다.

 

원본과 사본이 다소 다르다.

 

tCityNew는 tCity의 사본을 뜬 후 일부 레코드를 변경한 것이며 더 최신의 데이터를 가지고 있습니다.

이제 이 tCityNew의 변경된 데이터를 tCity에 병합하겠습니다.

이 경우는 tCity가 타깃이고 tCityNew가 소스입니다.

 

MERGE INTO tCity T USING tCityNew S ON (S.name = T.name) /* PK를 비교 */
WHEN MATCHED THEN
	UPDATE SET T.area = S.area, T.popu = S.popu
    /* 일치하면 area와 popu를 UPDATE */
WHEN NOT MATCHED THEN
	INSERT VALUES (S.name, S.area, S.popu, S.metro, S.region);
    /* 일치하지 않으면 삽입 */

 

잘 변경되었다-!

 

일치 조건으로 PK를 보통 사용합니다.

위 경우에는 name을 사용했습니다.

 

두 테이블의 레코드를 비교해서 일치하면 area와 popu를 원본에서 타깃 쪽으로 대입합니다.

UPDATE 할 테이블은 타깃이므로 따로 테이블을 적지 않아도 되고,

비교 조건도 이미 찾았으므로 WHERE절도 필요 없습니다.

단지 어떤 필드를 가져올지만 명시해주면 됩니다.

name이 일치하지 않는 경우에는 레코드를 새로 삽입합니다.

 

원본 테이블 전체를 다 병합하면 시간이 꽤 오래 걸립니다.

원본의 일부 조건에 맞는 레코드만 병합하고 싶다면 USING 절에 테이블명을 바로 쓰지 말고,

서브 쿼리로 조건을 제한하여 일부 레코드만 취합니다.

예를 들어 경기도에 있는 도시의 정보만 병합하고 싶다면 아래와 같이 쿼리를 쓰면 됩니다.

 

MERGE INTO tCity T
USING (SELECT * FROM tCityNew WHERE region = '경기') S /* 서브쿼리를 씁니다. */
ON (S.name = T.name)
WHEN MATCHED THEN
	UPDATE SET T.area = S.area, T.popu = S.popu
WHEN NOT MATCHED THEN
	INSERT VALUES (S.name, S.area, S.popu, S.metro, S.region);

 

주의할 점은 오라클은 MERGE 다음에 INTO 전치사와 ON 절의 괄호가 필수라는 점입니다.

SQL Server는 둘 다 없어도 무방하지만 쓰는 것이 좋겠습니다.

아쉽게도 MariaDB는 아직 MERGE가 지원되지 않습니다.

 

2. UPDATE FROM(SQL Server Only)

SQL Server만 지원하고 MERGE가 나온 뒤로는 잘 사용되지 않지만 그래도 알아는 두면 좋은 UPDATE FROM입니다.

MERGE와 비슷하게 타깃 테이블의 필드를 소스 테이블의 필드 값으로 일괄 갱신합니다.

 

UPDATE 타겟 SET 필드 = 소스.필드 FROM 소스 WHERE 병합조건

 

UPDATE 문에는 원래 FROM 절이 없지만 이 경우에는 원본 테이블을 지정하기 위해 FROM 절이 옵니다.

임시로 만든 tCityPopu 테이블로부터 값을 경신해보겠습니다.

 

 

UPDATE tCity SET popu = S.popu FROM tCityPopu AS S WHERE tCity.name = S.name;

 

춘천의 popu가 잘 들어갔다-!

 

3. MariaDB는 도대체 뭐가 되는 겁니까?

MariaDB는 MERGE도 안되고 UPDATE FROM도 지원을 하지 않습니다.

그렇지만 연관 서브 쿼리를 활용하면 됩니다.

 

UPDATE tCity SET popu = (SELECT popu FROM tCityPopu P WHERE P.name = tCity.name)
WHERE name IN (SELECT name FROM tCityPopu);

 

새 테이블에 있는 도시의 인구수를 읽어 원래 테이블의 popu 필드를 갱신합니다.

갱신 대상을 찾기 위해, 새 인구수를 찾기 위해 두 개의 서브 쿼리가 필요해 성능은 그다지 좋지 않습니다.

이 외에 조인 문으로도 병합을 처리할 수 있으며 성능은 오히려 더 좋습니다.

 

 

 

그만 알아보겠습니다.

 

 

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24