SQL Server 조인(join) 을 이용한 데이터 업데이트 방법

Select 결과를 이용하여 테이블 업데이트 하는 방법 (조인 업데이트)

데이터베이스 관리나 개발중에 다른 테이블의 데이터를 조인하여 데이터를 업데이트해야 하는 경우가 매우 많다.

만약 하나의 칼럼을 업데이트 하는 경우라면 서브쿼리를 사용할수도 있을것이다. 하지만 성능에 매우 좋지 않은 경우가 많다.

또한 업데이트 할 칼럼이 많을경우 쿼리문이 너무 길어지고 인덱스가 제대로 잡혀있지 않을경우 서버에도 부담이 되는 경우가 많다.

이런 상황을위해 SQL Server 에서 제공하는 조인 업데이트 방법이 있다. 구문 또한 select join 이나 마찬가지로 단순하며 조인에서 사용할수 있는 많은 실행계획을 사용하여 최적의 업데이트 성능을 확보할수 있게 된다.

테스트 테이블 생성

 
create table table_a(user_id varchar(100), name varchar(10));
insert into table_a(user_id, name) values('user1','name1')
insert into table_a(user_id, name) values('user2','name2')
insert into table_a(user_id, name) values('user3','name3')

create table table_b(user_id varchar(100), city varchar(100));
insert into table_b(user_id, city) values('user1','busan')
insert into table_b(user_id, city) values('user2','deojeon')
insert into table_b(user_id, city) values('user3','ulsan')

alter table table_a add city varchar(100);
 

일반적인 구문의 예

 
--alias 를 사용해서
UPDATE a
SET 
	city=b.city
FROM 
	table_a a 
	INNER JOIN
	table_b b ON a.user_id=b.user_id
WHERE user_id='user1'

--alias 없이
UPDATE table_a
SET 
	city=table_b.city
FROM 
	table_a 
	INNER JOIN 
	table_b ON table_a.user_id=table_b.user_id
WHERE
	user_id='user1';

--from 절에 join 쿼리 없이
UPDATE table_a
SET 
	city = b.city 
FROM (
    SELECT user_id, city
    FROM table_b WHERE user_id='user1') b
WHERE 
    table_a.user_id = b.user_id

MERGE INTO 구문 예

 --기본적인 merge into 구문을 이용한 업데이트
MERGE INTO table_a a
   USING table_b b
      ON a.user_id = b.user_id
         AND a.user_id = 'user1'
WHEN MATCHED THEN
   UPDATE 
      SET city = b.city;

--상대 테이블의 집계가 필요한 경우
MERGE INTO table_a a
   USING (
          SELECT user_id, city
            FROM table_b
           WHERE user_id = 'user2'
         ) b
      ON a.user_id = b.user_id
WHEN MATCHED THEN
   UPDATE 
      SET city = b.city;
 

CTE 구문 예

 
WITH test_cte
     AS (SELECT a.city,
                b.city AS bcity
         FROM   table_a a
                JOIN table_b b
                  ON a.user_id = b.user_id
       )

UPDATE test_cte
SET    city = bcity
WHERE city is null;