【MySQL】他のテーブルの値をINSERTする（INSERT~SELECT）

INSERT~SELECTを使って他のテーブルの値をINSERTする方法について紹介します。

前提

テーブルの準備

例としてUserとScoreと中間テーブルであるUserScoreの３つのテーブルを用意します。

UserとScoreはscoreIdで結合できるものとします。

SQL

-- Userテーブルの作成
CREATE TABLE User (
    userId INT PRIMARY KEY,
    name VARCHAR(100),
    scoreId INT,
    scoreValue INT
);

-- Scoreテーブルの作成
CREATE TABLE Score (
    scoreId INT PRIMARY KEY,
    value INT
);

-- UserテーブルとScoreテーブルの中間テーブルの作成
CREATE TABLE UserScore (
    userId INT PRIMARY KEY,
    scoreId INT,
    decision VARCHAR(100)
);

データの準備

UserテーブルとScoreテーブルにサンプルデータを投入します。

この２つのテーブルのデータに応じてUserScoreのデータを作成するため、UserScoreテーブルは何もデータがない状態とします。

Userテーブル

SQL

INSERT INTO User (userId, name, scoreId, decision, scoreValue) VALUES
(1, 'Alice', 1, NULL, NULL),
(2, 'Bob', 2, NULL, NULL),
(3, 'Charlie', 3, NULL, NULL);

userId	name	scoreId	scoreValue
1	Alice	1	NULL
2	Bob	2	NULL
3	Charlie	3	NULL

Scoreテーブル

SQL

INSERT INTO Score (scoreId, value) VALUES
(1, 75),
(2, 45),
(3, 60);

scoreId	value
1	75
2	45
3	60

Userテーブルの値をUserScoreテーブルにINSERTする

まずINSERTの基本構文はこちらです。

INSERTの基本構文

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT~SELECTではINSERTの基本構文にSELECT文をを付け加えて
INSERT INTO .... SELECT カラム名 FROM テーブル名の形にします。

SQL

INSERT INTO UserScore (userId, scoreId, decision)
SELECT u.userId, u.scoreId, NULL
FROM User u
JOIN Score s ON u.scoreId = s.scoreId;

実行結果は以下のようになります。

userId	scoreId	decision
1	1	NULL
2	2	NULL
3	3	NULL

Scoreテーブルの値に応じてUserScoreテーブルにINSERTする値を変える

CASE式を使って、取得データに応じてINSERTする値を変えることも可能です。

たとえばUserScoreテーブルのdecisionカラムに対して、Scoreテーブルのvalueが50以上の場合に"Approved"と、そうでない場合に"Pending"と入力したい場合のクエリは以下のようになります。

SQL

-- UserテーブルとScoreテーブルを結合してUserScoreテーブルにデータを挿入
INSERT INTO UserScore (userId, scoreId, decision)
SELECT u.userId, u.scoreId, 
       CASE
           WHEN s.value >= 50 THEN 'Approved'
           ELSE 'Pending'
       END AS decision
FROM User u
JOIN Score s ON u.scoreId = s.scoreId;

userId	scoreId	decision
1	1	Approved
2	2	Pending
3	3	Approved

（おまけ）Scoreテーブルの値に応じてUserScoreテーブルにUPDATEする値を変える

今回の趣旨とは異なりますが、他のテーブルの値をINSERTではなくUPDATEするの場合は以下のようになります。
JOIN句はUPDATEの直後にきます。

SQL

-- Scoreテーブルの値に応じてUserScoreテーブルにUPDATEする
UPDATE User u
JOIN Score s ON u.scoreId = s.scoreId
SET u.decision = CASE
    WHEN s.value >= 50 THEN 'Approved'
    ELSE 'Pending'
END;

このJOIN句はWHERE句に置き換えることも可能ですが、今回のケースでは冗長になってしまいます。
また、一般的にJOIN句を使う方がデータを読み込む量が減らせるため良いとされているので、JOIN句を使うのをオススメします。

SQL

-- Scoreテーブルの値に応じてUserScoreテーブルにUPDATEする
UPDATE User u
SET u.decision = CASE
    WHEN (SELECT s.value FROM Score s WHERE s.scoreId = u.scoreId) >= 50 THEN 'Approved'
    ELSE 'Pending'
END;