MSSQL UPDATE 조인(스칼라서브쿼리,중첩서브쿼리)

먼저 예제를 실행할수 있도록 테이블 스크립트를 복사하여 MSSQL 실행창에서 실행해주세요.
실행후 SSMS 실행창에 조회를 해본 결과값입니다.

-- 1. 부서(dept) 테이블 생성
CREATE TABLE dbo.dept (
    deptno INT PRIMARY KEY,
    dname  VARCHAR(14),
    loc    VARCHAR(13)
);

-- 2. 사원(emp) 테이블 생성
CREATE TABLE dbo.emp (
    empno    INT PRIMARY KEY,
    ename    VARCHAR(10),
    job      VARCHAR(9),
    mgr      INT,
    hiredate DATE,
    sal      NUMERIC(7,2),
    comm     NUMERIC(7,2),
    deptno   INT FOREIGN KEY REFERENCES dbo.dept(deptno)
);

-- 3. 부서(dept) 데이터 입력
INSERT INTO dbo.dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dbo.dept VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO dbo.dept VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO dbo.dept VALUES (40, 'OPERATIONS', 'BOSTON');

-- 4. 사원(emp) 데이터 입력
INSERT INTO dbo.emp VALUES (7369, 'SMITH',  'CLERK',     7902, '1980-12-17', 800,  NULL, 20);
INSERT INTO dbo.emp VALUES (7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-20', 1600, 300,  30);
INSERT INTO dbo.emp VALUES (7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250, 500,  30);
INSERT INTO dbo.emp VALUES (7566, 'JONES',  'MANAGER',   7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO dbo.emp VALUES (7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO dbo.emp VALUES (7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO dbo.emp VALUES (7782, 'CLARK',  'MANAGER',   7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO dbo.emp VALUES (7788, 'SCOTT',  'ANALYST',   7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO dbo.emp VALUES (7839, 'KING',   'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO dbo.emp VALUES (7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08', 1500, 0,    30);
INSERT INTO dbo.emp VALUES (7876, 'ADAMS',  'CLERK',     7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO dbo.emp VALUES (7900, 'JAMES',  'CLERK',     7698, '1981-12-03', 950,  NULL, 30);
INSERT INTO dbo.emp VALUES (7902, 'FORD',   'ANALYST',   7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO dbo.emp VALUES (7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300, NULL, 10);

1. UPDATE 조인 (Alias사용하여 UPDATE JOIN을 한경우)

UPDATE d
   SET d.loc = 'TEXAS'
  FROM dbo.dept d
 INNER JOIN dbo.emp e 
    ON d.deptno = e.deptno
 WHERE e.job = 'SALESMAN';

FROM절에서 dbo.dept에 d라는 별칭(Alias)을 주었다면, UPDATE 바로 뒤에도 반드시 원본 테이블명 대신 별칭 d를 적어주어야 MSSQL이 어떤 테이블을 업데이트해야 하는지 정확히 찾아낼 수 있습니다.

UPDATE dbo.dept --> 별칭을 사용하지않고 테이블명을 그래도 사용한 경우
   SET d.loc = 'TEXAS'
  FROM dbo.dept d
 INNER JOIN dbo.emp e 
    ON d.deptno = e.deptno
 WHERE e.job = 'SALESMAN';

2. 서브쿼리를 사용하여 JOIN 업데이트 처리하기

SET절에는 스칼라 서브쿼리를 사용하여 단일 값이 나오도록 하여 서브쿼리를 활용하여 업데이트를 할수 있다.

UPDATE dbo.dept    
   SET loc = (SELECT a.loc FROM dbo.dept a WHERE a.deptno = 20)
 WHERE deptno IN (10, 40)

WHERE절에는 중첩서브쿼리를 사용하여 여러개의 값이 결과값으로 나와서 상관없으므로 중첩서브쿼리를 활용하여 업데이트를 할수 있다.

UPDATE dbo.dept
   SET loc = 'LOS ANGELES'
 WHERE dept.deptno IN (SELECT a.deptno
                         FROM dbo.emp a
                        WHERE a.job = 'SALESMAN')