MySQL - UPDATE クエリと SELECT クエリの組み合わせ

2024-04-02

MySQL - SELECT クエリに基づいた UPDATE クエリ

MySQL では、SELECT クエリで取得した結果に基づいて、UPDATE クエリを実行することができます。これは、特定の条件に合致するレコードを効率的に更新したい場合に役立ちます。

基本的な構文

UPDATE テーブル名
SET 列名 = 値, 列名 = 値, ...
WHERE 条件;

例

customers テーブルの age 列が 30 歳以上の顧客の discount 列を 10% に更新する

UPDATE customers
SET discount = 10
WHERE age >= 30;

products テーブルの category 列が "書籍" で、かつ price 列が 1000 円以下の商品の stock 列を 10 個増加させる

UPDATE products
SET stock = stock + 10
WHERE category = '書籍' AND price <= 1000;

SELECT クエリとの組み合わせ

UPDATE クエリでは、WHERE 句だけでなく、SELECT クエリをサブクエリとして使用することで、より複雑な条件に基づいてレコードを更新することができます。

orders テーブルで、過去 7 日間に注文された商品の status 列を "発送済み" に更新する

UPDATE orders
SET status = '発送済み'
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

users テーブルで、login_count 列が 10 回以上のユーザーの last_login_date 列を本日の日付に更新する

UPDATE users
SET last_login_date = CURDATE()
WHERE login_count >= 10;

注意事項

サブクエリは、UPDATE クエリで更新するテーブルと同じテーブルを参照することはできません。
サブクエリは、1 つの行のみを返す必要があります。複数行を返す場合は、エラーが発生します。
JOIN を使用して複数のテーブルからデータを取得する場合は、サブクエリではなく、FROM 句でテーブルを指定する必要があります。

補足

上記の例は、基本的な使い方を説明するために簡略化されています。実際には、より複雑な条件や処理を行うこともできます。
UPDATE クエリを実行する前に、必ずバックアップを取るようにしてください。

-- サンプルデータ
INSERT INTO customers (name, age, discount) VALUES
('山田太郎', 35, 5),
('佐藤花子', 25, 0),
('田中一郎', 40, 15);

-- UPDATE クエリ
UPDATE customers
SET discount = 10
WHERE age >= 30;

-- 結果
SELECT * FROM customers;

-- 出力
+-------+------+---------+
| name  | age  | discount |
+-------+------+---------+
| 山田太郎 | 35  | 10      |
| 田中一郎 | 40  | 10      |
+-------+------+---------+

-- サンプルデータ
INSERT INTO products (name, category, price, stock) VALUES
('小説', '書籍', 800, 5),
('漫画', '書籍', 1200, 10),
('ゲーム', 'ゲーム', 2000, 20);

-- UPDATE クエリ
UPDATE products
SET stock = stock + 10
WHERE category = '書籍' AND price <= 1000;

-- 結果
SELECT * FROM products;

-- 出力
+-------+--------+-------+-------+
| name  | category | price | stock |
+-------+--------+-------+-------+
| 小説 | 書籍   | 800  | 15   |
| 漫画 | 書籍   | 1200 | 10   |
| ゲーム | ゲーム   | 2000 | 20   |
+-------+--------+-------+-------+

-- サンプルデータ
INSERT INTO orders (order_date, product_name, status) VALUES
('2024-03-23', '小説', '注文中'),
('2024-03-25', '漫画', '注文中'),
('2024-03-28', 'ゲーム', '注文中'),
('2024-03-30', '小説', '注文中');

-- UPDATE クエリ
UPDATE orders
SET status = '発送済み'
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 結果
SELECT * FROM orders;

-- 出力
+------------+------------+------+
| order_date | product_name | status |
+------------+------------+------+
| 2024-03-23 | 小説         | 発送済み |
| 2024-03-25 | 漫画         | 発送済み |
| 2024-03-28 | ゲーム         | 発送済み |
| 2024-03-30 | 小説         | 注文中 |
+------------+------------+------+

-- サンプルデータ
INSERT INTO users (name, login_count, last_login_date) VALUES
('山田太郎', 12, '2024-03-20'),
('佐藤花子', 5, '2024-03-25'),
('田中一郎', 15, '2024-03-28');

-- UPDATE クエリ
UPDATE users
SET last_login_date = CURDATE()
WHERE login_count >= 10;

-- 結果
SELECT * FROM users;

-- 出力
+-------+------------+------------+
| name  | login_count | last_login_date |
+-------+------------+------------+
| 山田太郎 | 12        | 2024-03-30 |
| 佐藤花子 | 5         | 2024-03-25 |
| 田中一郎 | 15        | 2024-03-30 |
+-------

他の方法

CASE 式

customers テーブルの discount 列を、年齢によって以下のように更新する

30 歳以上: 10%
それ以外: 0%

UPDATE customers
SET discount = CASE age
    WHEN >= 30 THEN 10
    WHEN >= 20 THEN 5
    ELSE 0
END;

UPDATE JOIN を使用して、複数のテーブルからデータを取得して更新することができます。

orders テーブルと products テーブルを結合して、orders テーブルの product_name 列に基づいて products テーブルの stock 列を更新する

UPDATE products p
JOIN orders o ON o.product_id = p.id
SET p.stock = p.stock - 1
WHERE o.order_status = '発送済み';

ストアドプロシージャを使用して、複雑な更新処理をカプセル化することができます。

DELIMITER //
CREATE PROCEDURE update_discount(IN customer_id INT, IN purchase_amount DECIMAL)
BEGIN
    UPDATE customers
    SET discount = CASE
        WHEN age >= 30 AND purchase_amount >= 1000 THEN 10
        WHEN age >= 20 AND purchase_amount >= 500 THEN 5
        ELSE 0
    END
    WHERE id = customer_id;
END //
DELIMITER ;