MySQLで3つのテーブルを結合してUPDATEする方法

2024-04-02

MySQLで3つのテーブル結合をUPDATEクエリで実行する方法

概要

前提条件

以下の内容を理解していることが必要です。

MySQLの基本的な構文
JOIN句の種類
UPDATEクエリの構文

手順

結合条件を決定する

まず、3つのテーブルをどのように結合するか、結合条件を決定する必要があります。結合条件は、共通する列に基づいて決定されます。例えば、以下の3つのテーブルがあるとします。

顧客情報テーブル (customers)
- 列: customer_id, name, email
注文情報テーブル (orders)
商品情報テーブル (products)
- 列: product_id, name, price

これらのテーブルを結合するには、以下のいずれかの方法を使用できます。

INNER JOIN: すべてのテーブルで一致するレコードのみを結合します。
LEFT JOIN: 左側のテーブルのすべてのレコードを返し、右側のテーブルと一致するレコードがあれば結合します。

結合条件が決まったら、UPDATEクエリを作成します。UPDATEクエリには、以下の要素が必要です。

UPDATE句: 更新するテーブルの名前を指定します。
SET句: 更新する列と新しい値を指定します。
JOIN句: 結合するテーブルと条件を指定します。
WHERE句: 更新するレコードを絞り込む条件を指定します。(オプション)

例:

UPDATE customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN products ON orders.product_id = products.product_id
SET customers.name = '山田太郎',
    orders.quantity = 10
WHERE products.name = '商品A';

このクエリは、以下の処理を実行します。

customersテーブルとordersテーブルをcustomer_idで結合します。
結合された結果から、productsテーブルとordersテーブルをproduct_idで結合します。
productsテーブルのname列が商品Aであるレコードのcustomersテーブルのname列を山田太郎に更新します。

補足

UPDATEクエリで複数の列を更新する場合は、SET句でカンマ区切りで列名を指定できます。
WHERE句はオプションです。WHERE句を省略すると、結合条件に合致するすべてのレコードが更新されます。
より複雑な結合を行う場合は、サブクエリを使用できます。

注意事項

UPDATEクエリを実行する前に、必ずバックアップを取るようにしてください。
結合条件を誤ると、意図しないレコードが更新される可能性があります。

-- テーブル定義
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- データ挿入
INSERT INTO customers (customer_id, name, email) VALUES
    (1, '山田太郎', '[email protected]'),
    (2, '佐藤花子', '[email protected]');

INSERT INTO orders (order_id, customer_id, product_id, quantity) VALUES
    (1, 1, 10, 1),
    (2, 2, 20, 2);

INSERT INTO products (product_id, name, price) VALUES
    (10, '商品A', 1000),
    (20, '商品B', 2000);

-- UPDATEクエリ
UPDATE customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN products ON orders.product_id = products.product_id
SET customers.name = '山田太郎2',
    orders.quantity = 10
WHERE products.name = '商品A';

-- 結果確認
SELECT * FROM customers;

-- 結果:
-- customer_id | name         | email
-- ------------ | ------------- | -------------
-- 1           | 山田太郎2     | [email protected]
-- 2           | 佐藤花子     | [email protected]

SELECT * FROM orders;

-- 結果:
-- order_id | customer_id | product_id | quantity
-- -------- | ------------ | ---------- | --------
-- 1        | 1           | 10         | 10
-- 2        | 2           | 20         | 2

実行結果

UPDATEクエリを実行後、customersテーブルとordersテーブルのデータが更新されます。

注意事項

このサンプルコードは、MySQL 8.0で動作確認しています。
テーブル名、列名、データ型などは、ご自身の環境に合わせて変更してください。

3つのテーブル結合をUPDATEクエリで実行する他の方法

サブクエリを使用して、結合条件を指定する方法です。

UPDATE customers
SET name = (
    SELECT name
    FROM orders
    INNER JOIN products ON orders.product_id = products.product_id
    WHERE products.name = '商品A'
    AND orders.customer_id = customers.customer_id
);

サブクエリで取得したcustomer_idと一致するcustomersテーブルのレコードのname列を、サブクエリで取得したname列に更新します。

CTE (Common Table Expressions)を使用する

WITH t AS (
    SELECT customers.*, orders.*
    FROM customers
    INNER JOIN orders ON customers.customer_id = orders.customer_id
)
UPDATE t
INNER JOIN products ON t.product_id = products.product_id
SET t.name = '山田太郎2',
    t.quantity = 10
WHERE products.name = '商品A';

CTE tとproductsテーブルをproduct_idで結合し、name列とquantity列を更新します。

一時テーブルを作成して、結合結果を保存してから更新する方法です。

-- 一時テーブル作成
CREATE TEMPORARY TABLE tmp (
    customer_id INT,
    name VARCHAR(255),
    quantity INT
);

-- 一時テーブルへのデータ挿入
INSERT INTO tmp
SELECT customers.customer_id, customers.name, orders.quantity
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

-- 一時テーブルの更新
UPDATE tmp
INNER JOIN products ON tmp.product_id = products.product_id
SET tmp.name = '山田太郎2',
    tmp.quantity = 10
WHERE products.name = '商品A';

-- customersテーブルの更新
UPDATE customers
INNER JOIN tmp ON customers.customer_id = tmp.customer_id
SET customers.name = tmp.name,
    customers.quantity = tmp.quantity;

-- 一時テーブル削除
DROP TEMPORARY TABLE tmp;