2021-12-14 23:38 작성

My SQL basic 메모

Table of contents

MySQL UPDATE JOIN 문법

MySQL에서 교차 table 업데이트를 달성하기 위해 UPDATE 구문 안에 JOIN 절을 사용할 수 있다.

다음은 UPDATE JOIN의 기본 문법이다:

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

MySQL UPDATE JOIN 문법을 더 자세히 살펴보자:

  • 첫째, UPDATE 절 다음에 메인 table(T1)을 명시하고 메인 table에 join하고자 하는 다른 table(T2)을 명시한다.
  • 그 다음, 사용하고자 하는 join의 종류(INNER JOIN 혹은 LEFT JOIN)을 명시하고 join 서술부를 더한다. JOIN 절은 UPDATE 절 바로 직후에 나타나야 한다.
  • 그리고 업데이트 하고자 하는 T1 그리고/혹은 T2 tables에 새로운 values를 할당한다.
  • 그 후에 업데이트 하고자 하는 rows를 제한하기 위해 WHERE 절에 조건을 할당한다.

UPDATE 구문 튜토리얼을 따르다 보면 다음의 문법을 사용해 다른 방식으로 교차 table 업데이트를 할 수 있다는 것을 눈치챌 것이다.

UPDATE T1, T2
SET T1.c2 = T2.c2,
    T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition;

이 UPDATE 구문은 INNER JOIN을 내재한 UPDATE JOIN과 동일하게 작동한다. 상기의 구문은 다음과 같이 다시 작성할 수 있다:

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

이해를 더 높이기 위해 UPDATE JOIN을 사용한 다른 예시를 살펴보자.



MySQL UPDATE JOIN 예시

증명을 위해 empdb라는 명칭의 새로운 샘플 데이터베이스를 사용할 것이다. 이 샘플 데이터베이스는 두개의 tables를 구성한다:

  • employees table은 employee id, name, performance 그리고 salary를 가진 employee 데이터를 저장한다.
  • merits table은 employee performance와 merit의 percentage를 저장한다.

다음의 구문들은 empdb 샘플 데이터베이스에서 데이터를 생성하고 불러온다.

CREATE DATABASE IF NOT EXISTS empdb;

USE empdb;

-- create tables
CREATE TABLE merits (
    performance INT(11) NOT NULL,
    percentage FLOAT NOT NULL,
    PRIMARY KEY (performance)
);

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

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

-- insert data for employees table
INSERT INTO employees(emp_name, performance, salary)
VALUES
CREATE DATABASE IF NOT EXISTS empdb;

USE empdb;

-- create tables
CREATE TABLE merits (
    performance INT(11) NOT NULL,
    percentage FLOAT NOT NULL,
    PRIMARY KEY (performance)
);

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

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

-- insert data for employees table
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);


INNER JOIN 절과 MySQL UPDATE JOIN 예시

employees의 퍼포먼스에 기반해 salary를 조절한다고 가정하자.

merit의 percentages는 merits table에 저장되어 있다. 그래서 UPDATE INNER JOIN 구문을 사용하여 merits table에 저장된 percentage에 기반한 employees table의 salary를 조절해야 한다.

employees와 merit tables 간의 연결고리는 performance다. 다음의 쿼리를 보자:

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

output:

emp_id emp_name performance salary
1 Mary Doe 1 50000
2 Cindy Smith 3 66950
3 Sue Greenspan 4 78750
4 Grace Dell 5 135000
5 Nancy Johnson 3 87550
6 John Doe 2 45450
7 Lily Bush 3 56650

employee table에서만 데이터를 업데이트할 것이므로 UPDATE 절 다음에 employees table만 명시한다.

employees table에서 각 row에 대해 쿼리는 performance의 value를 merits table에 있는 performance column의 value와 확인한다. 만약 서로 매칭이 되면, merits table에서 percentage를 가져와 employees table의 salary column을 업데이트 한다.

UPDATE 구문에서 WHERE 절을 제외했기 때문에 employees table의 모든 기록이 업데이트 된다.


LEFT JOIN과 MySQL UPDATE JOIN 예시

company에서 두 명의 employees를 추가적으로 채용했다고 가정하자:

INSERT INTO employees(emp_name, performance, salary)
VALUES
    ('Jack William', NULL, 43000),
    ('Ricky Bond', NULL, 52000);
emp_id emp_name performance salary
1 Mary Doe 1 50000
2 Cindy Smith 3 66950
3 Sue Greenspan 4 78750
4 Grace Dell 5 135000
5 Nancy Johnson 3 87550
6 John Doe 2 45450
7 Lily Bush 3 56650
8 Jack William NULL 43000
9 Ricky Bond NULL 52000

새로 고용된 이들의 salary를 증가시키는데 있어 UPDATE INNER JOIN 구문을 사용할 수 없다. merit table의 performance 데이터를 사용할 수 없기 때문이다. 여기서 UPDATE LEFT JOIN이 구원투수로 등장한다.

UPDATE LEFT JOIN 구문은 기본적으로 다른 table에서 일치하는 row가 없을 때 임의의 row를 업데이트 하는데 사용 된다.

예를 들어, 다음의 구문을 통해 새로 입사한 직원들에게 1.5%의 salary 인상을 적용할 수 있다.

UPDATE employees
        LEFT JOIN   
    merits ON employees.performance = merits.performance
SET
    salary = salary | salary * 0.015
WHERE
    merits.percentage IS NULL;

output:

emp_id emp_name performance salary
1 Mary Doe 1 50000
2 Cindy Smith 3 66950
3 Sue Greenspan 4 78750
4 Grace Dell 5 135000
5 Nancy Johnson 3 87550
6 John Doe 2 45450
7 Lily Bush 3 56650
8 Jack William NULL 43645
9 Ricky Bond NULL 52780