仕入れ先から新しい価格表が届き、商品テーブルの価格をまとめて書きかえたい場面があります。こういうときは、UPDATE に JOIN を組み合わせると1文で更新できます。別のテーブルの値をそのまま使えるので、商品ごとに UPDATE を何十回も書く必要はありません。
書き方そのものは短いのですが、UPDATE は実行すると元に戻しにくい操作です。そこで、実行前に同じ JOIN を SELECT で流して対象の行を確かめる手順から始めます。集計した結果で更新する方法、LEFT JOIN で相手のテーブルにない行だけを更新する方法、サブクエリで書いたときに一致しない行が NULL で上書きされる落とし穴も、実行結果で確かめます。
以下の結果は MySQL 8.0 で実際に実行したものです。
例に使う商品テーブルと新しい価格表
文房具の商品テーブル（products）と、仕入れ先から届いた新しい価格表（price_updates）を使います。価格表には、商品テーブルにない ID 9 の行と、今と同じ価格の ID 2 の行をわざと入れてあります。消しゴム（ID 3）とハサミ（ID 5）は価格表に載っていません。
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price INT
);
CREATE TABLE price_updates (
product_id INT PRIMARY KEY,
new_price INT NOT NULL
);
INSERT INTO products VALUES
(1, 'ノート', 300),
(2, 'ボールペン', 150),
(3, '消しゴム', 100),
(4, '定規', 200),
(5, 'ハサミ', 450);
INSERT INTO price_updates VALUES
(1, 320),
(2, 150),
(4, 220),
(9, 999);やりたいのは、価格表にある商品だけ価格を新しくして、載っていない商品はそのままにすることです。
更新する前に SELECT で対象を確かめる
UPDATE を書く前に、同じ結合条件で SELECT を実行します。ここで出た行が、そのまま UPDATE で書きかわる行になります。今の値と新しい値を横に並べておくと、取り違えにすぐ気づけます。
SELECT p.product_id, p.name, p.price AS 今の価格, u.new_price AS 新しい価格
FROM products p
JOIN price_updates u ON p.product_id = u.product_id;+------------+-----------------+--------------+-----------------+
| product_id | name | 今の価格 | 新しい価格 |
+------------+-----------------+--------------+-----------------+
| 1 | ノート | 300 | 320 |
| 2 | ボールペン | 150 | 150 |
| 4 | 定規 | 200 | 220 |
+------------+-----------------+--------------+-----------------+出てきたのは3行です。商品テーブルにない ID 9 は、内部結合（INNER JOIN）なので出てきません。価格表にない ID 3 と 5 も出てこないので、更新されないことがここでわかります。件数が想定と違うときは、UPDATE に進む前に結合条件を見直してください。
UPDATE … JOIN の基本の書き方
MySQL では、UPDATE のすぐあとに JOIN を書き、SET で更新する列と値を指定します。形は次のとおりです。
- UPDATE のあとに、更新するテーブルを書く
- JOIN と ON で、値を持ってくるテーブルと結合条件を書く
- SET で「どのテーブルのどの列」に「どの値」を入れるかを書く
- しぼりこみが必要なら WHERE を足す
先ほどの SELECT を、そのまま UPDATE に書きかえます。
UPDATE products p
JOIN price_updates u ON p.product_id = u.product_id
SET p.price = u.new_price;
SELECT ROW_COUNT();
SELECT * FROM products;+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
+------------+-----------------+-------+
| product_id | name | price |
+------------+-----------------+-------+
| 1 | ノート | 320 |
| 2 | ボールペン | 150 |
| 3 | 消しゴム | 100 |
| 4 | 定規 | 220 |
| 5 | ハサミ | 450 |
+------------+-----------------+-------+結合できた3行のうち、ROW_COUNT()（直前の文で変わった行数）は2です。ボールペンは今と同じ 150 を入れようとしたので、MySQL は変更なしとして数えません。mysql コマンドで実行すると「Rows matched: 3 Changed: 2」のように、条件に合った行数と実際に変わった行数が別々に出ます。消しゴムとハサミは価格表にないので、元の価格のままです。
ほかのデータベースでは書き方が少し違います。
| データベース | 別テーブルの値で更新する書き方 |
|---|---|
| MySQL | UPDATE products p JOIN price_updates u ON … SET p.price = u.new_price |
| PostgreSQL | UPDATE products p SET price = u.new_price FROM price_updates u WHERE p.product_id = u.product_id |
| SQL Server | UPDATE p SET p.price = u.new_price FROM products p JOIN price_updates u ON … |
PostgreSQL の SET では、更新する列にテーブルの別名を付けません（price = … と書きます）。MySQL の書き方のまま持っていくとエラーになるので注意してください。
集計した結果で更新する
結合する相手は、ふつうのテーブルでなくてもかまいません。GROUP BY で集計したサブクエリを FROM 句のテーブル（派生テーブル）として JOIN すれば、集計結果を使って更新できます。ここでは、注文の合計が1万円以上の顧客の会員ランクをゴールドにします。
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
rank_name VARCHAR(10) NOT NULL DEFAULT '一般'
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount INT NOT NULL
);
INSERT INTO customers (customer_id, name) VALUES
(1, '佐藤'), (2, '鈴木'), (3, '高橋'), (4, '田中');
INSERT INTO orders VALUES
(101, 1, 8000),
(102, 1, 5000),
(103, 2, 3000),
(104, 3, 12000),
(105, 2, 1500);ここでも先に SELECT で対象を確かめます。
SELECT c.customer_id, c.name, t.total
FROM customers c
JOIN (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) t ON c.customer_id = t.customer_id
WHERE t.total >= 10000;+-------------+--------+-------+
| customer_id | name | total |
+-------------+--------+-------+
| 1 | 佐藤 | 13000 |
| 3 | 高橋 | 12000 |
+-------------+--------+-------+佐藤さんと高橋さんの2人です。SELECT の列の部分を SET に置きかえれば、UPDATE になります。
UPDATE customers c
JOIN (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) t ON c.customer_id = t.customer_id
SET c.rank_name = 'ゴールド'
WHERE t.total >= 10000;orders をそのまま JOIN すると、佐藤さんのように注文が2件ある顧客は2行に増えます。合計で判断したいときは、このように先に GROUP BY でまとめてから結合します。
LEFT JOIN で相手のテーブルにない行だけを更新する
外部結合（LEFT JOIN）と IS NULL を組み合わせると、相手のテーブルに一致する行がない行だけを更新できます。注文が1件もない顧客を休眠にする例です。
UPDATE customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
SET c.rank_name = '休眠'
WHERE o.customer_id IS NULL;
SELECT * FROM customers;+-------------+--------+--------------+
| customer_id | name | rank_name |
+-------------+--------+--------------+
| 1 | 佐藤 | ゴールド |
| 2 | 鈴木 | 一般 |
| 3 | 高橋 | ゴールド |
| 4 | 田中 | 休眠 |
+-------------+--------+--------------+注文のない田中さんだけが休眠になりました。LEFT JOIN では、一致する行がないと orders 側の列がすべて NULL になります。WHERE o.customer_id IS NULL は、その「一致しなかった行」だけを選ぶ条件です。結合の種類ごとの違いは「INNER JOIN・LEFT JOIN・RIGHT JOINの違いと使い分け（MySQL実例つき）」で説明しています。
よくある落とし穴
UPDATE … JOIN でつまずきやすい点を表にまとめます。
| 落とし穴 | 起きること | 直し方 |
|---|---|---|
| サブクエリで SET の値を作る | 一致しない行が NULL で上書きされる | JOIN で書く。サブクエリのままなら WHERE EXISTS で対象をしぼる |
| 更新するテーブルをサブクエリでも読む | ERROR 1093 で止まる | サブクエリを派生テーブルにして JOIN する |
| LIMIT や ORDER BY を付ける | ERROR 1221 で止まる | 主キーの範囲で分けて実行する |
| 結合先に同じキーの行が2つ以上ある | どちらの値が入るか決まらない | 事前に GROUP BY と HAVING で重複を調べる |
一番気をつけたいのはサブクエリの書き方です。同じ価格の更新を、相関サブクエリ（外側の行ごとに実行されるサブクエリ）で書いてみます。テーブルは、価格を書きかえる前の products をコピーした products_sub を使いました。
UPDATE products_sub s
SET s.price = (
SELECT u.new_price FROM price_updates u
WHERE u.product_id = s.product_id
);
SELECT * FROM products_sub;+------------+-----------------+-------+
| product_id | name | price |
+------------+-----------------+-------+
| 1 | ノート | 320 |
| 2 | ボールペン | 150 |
| 3 | 消しゴム | NULL |
| 4 | 定規 | 220 |
| 5 | ハサミ | NULL |
+------------+-----------------+-------+WHERE がないので、全部の行が更新の対象になります。価格表にない消しゴムとハサミは、サブクエリの結果が0行になり、価格が NULL で消えてしまいました。JOIN で書けば、一致しない行はそもそも対象に入りません。
更新するテーブル自身をサブクエリで読むと、MySQL はエラーにします。平均より安い商品を10円値上げする例です。
UPDATE products
SET price = price + 10
WHERE price < (SELECT AVG(price) FROM products);ERROR 1093 (HY000): You can't specify target table 'products' for update in FROM clause平均を派生テーブルにして JOIN すると通ります。平均は 248 円なので、ボールペン・消しゴム・定規の3つが10円上がりました。
UPDATE products p
JOIN (SELECT AVG(price) AS avg_price FROM products) a
ON p.price < a.avg_price
SET p.price = p.price + 10;
SELECT * FROM products;+------------+-----------------+-------+
| product_id | name | price |
+------------+-----------------+-------+
| 1 | ノート | 320 |
| 2 | ボールペン | 160 |
| 3 | 消しゴム | 110 |
| 4 | 定規 | 230 |
| 5 | ハサミ | 450 |
+------------+-----------------+-------+複数のテーブルを使う UPDATE には、LIMIT と ORDER BY を付けられません。付けると次のエラーになります。
UPDATE products p
JOIN price_updates u ON p.product_id = u.product_id
SET p.price = u.new_price
LIMIT 1;ERROR 1221 (HY000): Incorrect usage of UPDATE and LIMIT何十万行も一度に更新するとロックが長く続くので、WHERE p.product_id BETWEEN 1 AND 10000 のように主キーの範囲で分けて流します。分けて実行する手順は「大量データのDELETE・UPDATEをLIMITで分割する方法」にまとめています。
結合先に同じキーの行が2つあっても、更新される行は1回しか書きかわりません。ただし、どちらの行の値が入るかは決まっていません。CSV を取りこんだばかりのテーブルのように主キーがない場合は、先に次の SELECT で重複を調べておきます。今回の price_updates は product_id が主キーなので、結果は0行でした。
SELECT product_id, COUNT(*) AS 件数
FROM price_updates
GROUP BY product_id
HAVING COUNT(*) > 1;外部キーでつながった親と子のテーブルを1つの UPDATE で両方書きかえると、MySQL が処理する順番によっては失敗してロールバックされます。公式リファレンスでも、この場合は片方のテーブルだけを更新し、ON UPDATE CASCADE に任せる書き方を勧めています。
まとめ
別テーブルの値で更新するときは、まず同じ JOIN を SELECT で流して、対象の行と件数を確かめます。問題がなければ、列の部分を SET に置きかえて UPDATE … JOIN にします。集計結果で更新するなら GROUP BY した派生テーブルと結合し、相手にない行を更新するなら LEFT JOIN と IS NULL を使います。サブクエリで SET の値を作ると一致しない行が NULL になるので、迷ったら JOIN で書いてください。
もっと学ぶなら【PR】
- スッキリわかるSQL入門 第4版：練習問題がたくさんある入門書。手を動かして基本を固めたい人向け
※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 リファレンスマニュアル: UPDATE Statement
- MySQL 8.0 リファレンスマニュアル: ROW_COUNT()
- PostgreSQL ドキュメント: UPDATE
アイキャッチ画像: Photo by Artem Beliaikin on Unsplash
