[MySQL] UPDATE JOIN - 교차 테이블 갱신

개요

데이터베이스를 다룰 때에 UPDATE를 수행할 때는 단순하게 단일 테이블에 대해서만 수행을 했었는데, 현재 사용 중인 프로시저를 분석하던 중 UPDATE 문에서 JOIN을 사용하는 문법을 발견했다.

이번 글에서 UPDATE JOIN 구문이 어떻게 동작하는지 정리해 둔다.

 

 

UPDATE JOIN

UPDATE JOIN은 다른 테이블의 값을 기반으로 한 테이블을 업데이트하는 등의 작업을 할 때 사용할 수 있는데, 그 구문은 다음과 같다.

UPDATE T1
[INNER JOIN | LEFT JOIN] T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition;

 

동작 흐름

1. UPDATE 키워드 뒤에 갱신할 테이블 지정한다.
2. JOIN 절 사용한다. JOIN 절은 UPDATE 절 바로 뒤에 나와야 한다.
3. 갱신할 테이블에 신규 값을 지정한다.
4. WHERE 절로 갱신할 행을 필터링한다.

 

사용 예시

예시로 다음과 같은 두 개 테이블이 있다고 하자.

 

merits

| performance | percentage |
| ----------- | ---------- |
| 1           | 0.00       |
| 2           | 0.01       |
| 3           | 0.03       |
| 4           | 0.05       |
| 5           | 0.08       |

 

employees

| emp_id | emp_name      | performance | salary    |
| ------ | ------------- | ----------- | --------- |
| 1      | Mary Doe      | 1           | 50000.00  |
| 2      | Cindy Smith   | 3           | 65000.00  |
| 3      | Sue Greenspan | 4           | 75000.00  |
| 4      | Grace Dell    | 5           | 125000.00 |
| 5      | Nancy Johnson | 3           | 85000.00  |
| 6      | John Doe      | 2           | 45000.00  |
| 7      | Lily Bush     | 3           | 55000.00  |

 

CREATE/INSERT문

CREATE TABLE merits (
  performance INT PRIMARY KEY, 
  percentage DEC(11, 2) NOT NULL
);

CREATE TABLE employees (
  emp_id INT AUTO_INCREMENT PRIMARY KEY, 
  emp_name VARCHAR(255) NOT NULL, 
  performance INT DEFAULT NULL, 
  salary DEC(11, 2) DEFAULT NULL, 
  FOREIGN KEY (performance) REFERENCES merits (performance)
);

INSERT INTO merits(performance, percentage) 
VALUES 
  (1, 0), 
  (2, 0.01), 
  (3, 0.03), 
  (4, 0.05), 
  (5, 0.08);

INSERT INTO employees(emp_name, performance, salary) 
VALUES 
  ('Mary Doe', 1, 50000), 
  ('Cindy Smith', 3, 65000), 
  ('Sue Greenspan', 4, 75000), 
  ('Grace Dell', 5, 125000), 
  ('Nancy Johnson', 3, 85000), 
  ('John Doe', 2, 45000), 
  ('Lily Bush', 3, 55000);

이때, 직원의 급여를 성과에 따라 일정 비율로 인상하고 싶다. 다시 말해, merits를 참조하여 employees의 salary를 업데이트하고 싶다. 

그 쿼리는 다음과 같이 작성할 수 있다.

UPDATE employees
  JOIN merits
    ON employees.performance = merits.performance
   SET employees.salary = employees.salary + employees.salary * merits.percentage
;

쿼리를 실행하여 갱신된 결과는 다음과 같다.

| emp_id | emp_name      | performance | salary    |
| ------ | ------------- | ----------- | --------- |
| 1      | Mary Doe      | 1           | 50000.00  |
| 2      | Cindy Smith   | 3           | 66950.00  |
| 3      | Sue Greenspan | 4           | 78750.00  |
| 4      | Grace Dell    | 5           | 135000.00 |
| 5      | Nancy Johnson | 3           | 87550.00  |
| 6      | John Doe      | 2           | 45450.00  |
| 7      | Lily Bush     | 3           | 56650.00  |

performance가 1이 아니었던 직원의 salary가 다음과 같이 갱신되었음을 알 수 있다.

- Cindy Smith : 65000 -> 66950
- Sue Greenspan : 75000 -> 78750
- Grace Dell : 125000 -> 135000.00
- Nancy Johnson : 85000 -> 87550.00
- John Doe : 45000 -> 45450.00

- Lily Bush : 55000 -> 56650.00

 

 

참고 문서

https://mauvpark.github.io/docs/Sql/MySQL/2021-12-14-e-mysql29.html

https://www.mysqltutorial.org/mysql-basics/mysql-update-join/