9-5.테이블 병합

1.MERGE

데이터를 안전하게 관리하려면 정기적으로 백업을 떠 두어야 한다. 또 테이블을 대량으로 변경하기 전에도 혹시 모를 사고를 예방하기 위해 사본을 떠 두고 변경한다. 이런 저런 이유로 사본에 대한 작업을 할 경우가 많다.

데이터는 늘 변하는 것이어서 시간이 흐를수록 원본과 사본은 차이가 많이 벌어진다. tCity 테이블에 대해 이런 상황을 만들어 보자. 도시 정보를 재조사할 수도 있고 시차를 두고 조사한 두 벌의 데이터가 존재할 수도 있다. 먼저 사본을 뜬다.

오라클, MariaDB : CREATE TABLE tCityNew AS SELECT * FROM tCity;

SQL Server : SELECT * INTO tCityNew FROM tCity;

사본인 tCityNew를 편집하여 여러 가지 변화를 주어 보자. 서울의 인구, 부산의 면적을 변경하고 춘천은 삭제하고 이천을 삽입한다. 두 테이블의 데이터가 조금씩 다르다.

UPDATE tCityNew SET popu = 1000 WHERE name = '서울';

UPDATE tCityNew SET area = 900 WHERE name = '부산';

DELETE FROM tCityNew WHERE name = '춘천';

INSERT INTO tCityNew VALUES ('이천',461,21,'n','경기');

사본에 대한 작업이 무사히 완료되었다면 변경한 데이터를 원본으로 다시 가져온다. 정확하게 똑같이 만들어야 한다면 덮어 써 버리면 되지만 때로는 선별적으로 가져와야 할 때도 있다. 두 테이블을 동기화하려면 뭐가 바뀌었는지 일일이 살펴보고 바뀐 건 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)

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);

일치 조건은 두 테이블의 name 필드를 사용한다. 기본키는 잘 변경하지 않아 테이블간의 비교에 적합하다. 두 테이블의 레코드를 비교하여 이름이 일치하면 면적과 인구수를 원본에서 타겟쪽으로 대입한다. 모든 필드를 다 복사할 필요는 없으며 주로 바뀌는 필드만 복사하면 된다. 도시 정보를 재조사해도 웬만해서는 소속 지역이 바뀌지는 않는다.

이 처리에 의해 새로 조사한 서울의 인구수와 부산의 면적을 타겟에 업데이트한다. 업데이트할 대상 테이블은 당연히 타겟이므로 UPDATE 문에 테이블명을 적을 필요 없다. 또한 비교 조건에 일치하는 레코드를 이미 찾았으므로 WHERE 절도 필요 없다. 어떤 필드를 가져올 것인지만 지정하면 된다.

이름이 일치하지 않을 경우는 새로 삽입한다. 원본에 새로 삽입한 이천은 타겟에 원래 없었던 레코드이며 삽입하여 동기화한다. 원본에서 삭제한 춘천은 타겟에는 그대로 남아 있으므로 별 처리를 하지 않는다. 병합 후 tCity의 레코드 개수는 9개가 된다. 어떤 값이 어디로 이동하는지 정리해 보자.

MERGE는 두 테이블의 name 필드를 순서대로 비교한다. 양쪽에 모두 있는 서울을 먼저 찾아 인구와 면적을 복사한다. 다음으로 부산을 찾아 역시 두 필드를 복사한다. 오산, 청주, 전주 등은 변화는 없지만 양쪽 모두에 있으므로 같은 값을 다시 덮어 쓴다.

이천은 원본에만 있고 타겟에는 없으니 새로 삽입한 레코드임을 알 수 있다. 원본에 없는 정보이니 UPDATE로 갱신할 수는 없다. NOT MATCHED에서 이 레코드의 필드를 읽어 타겟에 삽입한다. 병합을 완료한 후의 tCity는 원래 정보에 tCityNew의 편집된 정보를 통합하여 가진다.

원본 테이블 전체를 다 병합하면 시간이 꽤 오래 걸린다. 원본의 일부 조건에 맞는 레코드만 병합하고 싶다면 USING 절에 테이블명을 바로 쓰지 말고 서브쿼리로 조건을 제한하여 일부 레코드만 취한다. 예를 들어 경기도에 있는 도시의 정보만 병합하고 싶다면 tCityNew에서 region이 경기도인 레코드만 읽는다.

MERGE INTO tCity T USING (SELECT * FROM tCityNew WHERE region = '경기') S ON (S.name = T.name)

....

이렇게 하면 서울의 면적을 갱신하고 이천시도 삽입하지만 부산의 인구수는 병합하지 않는다. 테이블 전체 대신 서브쿼리로 일부 레코드만 읽으면 되니 아주 쉽다. 그러나 잘못 생각하면 ON 절이 조건이니 여기다 조건문을 작성할 위험이 있다.

MERGE INTO tCity T USING tCityNew S ON (S.name = T.name AND S.region = '경기')

....

이 코드는 에러이다. ON 절의 일치 조건은 두 테이블의 대응 레코드를 찾는 것이지 병합할 대상에 대한 조건을 지정하는 것이 아니다.

호환성 체크

오라클은 MERGE 다음의 INTO 전치사와 ON 절의 괄호가 필수이다. SQL Server는 둘 다 없어도 무방하다. MariaDB는 아직 MERGE를 지원하지 않는다.

연습 문제

9.앞 실습에서 만들었던 사본 tStaff2를 tStaff에 병합하라.

10.tStaff2의 대리급 사원에 대해서만 병합하라.

2.UPDATE FROM

다음은 좀 더 단순한 예로 실습을 해 보자. 도시의 인구만 재조사한다면 다른 필드는 필요 없고 어느 도시의 인구가 얼마인지만 기록하면 된다. 다음과 같이 인구 조사용 테이블을 간단하게 디자인하고 몇 개 도시의 인구를 새로 조사했다.

CREATE TABLE tCityPopu

(

name CHAR(10) PRIMARY KEY,

popu INT NULL

);

INSERT INTO tCityPopu VALUES ('서울',1000);

INSERT INTO tCityPopu VALUES ('부산',500);

INSERT INTO tCityPopu VALUES ('춘천',100);

이렇게 조사한 인구 통계는 최종적으로 tCity 테이블에 병합하여 갱신해야 한다. 두 테이블이 아무리 커도 MERGE 명령으로 간단하게 병합할 수 있다.

MERGE INTO tCity C USING tCityPopu P ON (C.name = P.name)

WHEN MATCHED THEN UPDATE SET C.popu = P.popu;

두 테이블을 도시명으로 비교하여 일치하는 행에 대해, 즉 새로 조사한 도시에 대해 popu 필드를 읽어 tCity의 인구를 갱신한다. 조사하지 않은 도시의 인구수는 원래값을 가진다. 인구만 조사했으므로 다른 필드는 건드릴 필요가 없으며 다른 필드값이 있지도 않다.

일치하지 않을 때의 동작도 지정할 수 있지만 병합만 한다면 굳이 그럴 필요는 없다. 원본에 없던 도시의 인구를 새로 조사했다면 삽입해 넣을 수 있지만 인구 정보만으로는 tCity가 요구하는 정보를 다 채울 수 없어 별 의미가 없다.

MERGE 명령 외에 SQL Server는 테이블을 병합하는 UPDATE FROM 명령을 추가로 제공한다. 기본 형식은 다음과 같으며 타겟 테이블의 필드를 원본 테이블의 필드값으로 일괄 갱신한다.

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

UPDATE 문에는 원래 FROM 절이 없지만 이 경우에는 원본 테이블을 지정하기 위해 FROM 절이 온다. FROM 절에 뷰나 서브쿼리를 쓸 수도 있다. 다음 명령은 tCityPopu의 popu(새 인구수)를 읽어 tCity의 popu(원래 인구수)를 갱신한다.

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

보고서에 있는 도시만 갱신하기 위해 WHERE 절에서 두 테이블의 도시명이 같은 행만 선택했다. 새 테이블에 조사하지 않은 도시는 원래값을 유지한다.

name 필드가 양쪽 테이블에 다 있어 소속을 분명히 밝혀야 한다. 테이블 두 개를 참조하다 보니 쿼리문이 길어지는데 FROM 절의 테이블에 별명을 붙여 두면 필드를 짧게 칭할 수 있어 편리하다.

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

그러나 원본 테이블에는 별명을 줄 수 없어 다음 구문은 안된다. UPDATE 다음의 대상 테이블은 원래 이름대로 써야 한다.

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

과거 대량 업데이트시에 가끔 사용했지만 더 범용적이고 편리한 MERGE 명령이 도입되어 요즘은 잘 사용하지 않는다. SQL Server만 지원하여 호환성도 좋지 않고 갱신만 할 뿐 삽입은 하지 못하는 한계도 있다. 그러나 과거에 작성한 쿼리를 참고하기 위해 명령 자체는 알아 두어야 한다.

그렇다면 MariaDB의 경우는 어떨까? MariaDB는 아직 MERGE 명령을 지원하지 않으며 UPDATE FROM 명령도 없다. 그렇다고 해서 병합을 못하는 것은 아니며 연관 서브쿼리를 활용하면 된다. 다음 명령은 똑같은 처리를 하며 세 DBMS에서 모두 잘 동작한다.

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

WHERE name IN (SELECT name FROM tCityPopu)

새 테이블에 있는 도시의 인구수를 읽어 원래 테이블의 popu 필드를 갱신한다. WHERE 조건을 빼 버리면 새 테이블에 없는 도시의 인구가 NULL로 바뀌어 버리므로 조건이 반드시 필요하다. 갱신 대상을 찾기 위해, 새 인구수를 찾기 위해 두 개의 서브쿼리가 필요해 성능은 그다지 좋지 않다.

이 외에 조인문으로도 병합을 처리할 수 있으며 성능은 오히려 더 좋다. 똑같은 문제라도 해결하는 방식은 항상 여러 가지가 있다. DBMS의 지원 명령 중에 속도, 빈도, 편의성 등을 고려하여 상황에 가장 적합한 방법을 잘 선택해 사용하면 된다.