【SQLテクニック集】CASE WHEN/THEN/ELSEでスマート更新！在庫切れ防止・顧客ステータス自動更新も楽々
MySQLにおけるUPDATE文でのCASE WHEN/THEN/ELSE構文の詳細解説
CASE WHEN/THEN/ELSE構文は、条件に応じて異なる値を更新する機能を提供します。これは、複数の条件を効率的に処理し、複雑な更新ロジックを簡潔に記述するのに役立ちます。
構文
UPDATE table_name
SET column_name =
CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
...
ELSE default_value
END
WHERE condition;
解説
table_name: 更新対象のテーブル名を指定します。column_name: 更新対象の列名を指定します。condition1,condition2, ...: 各WHEN`節で評価される条件式を指定します。value1,value2, ...`: 各条件が真の場合に更新される値を指定します。default_value: すべての条件が偽の場合に更新される値を指定します。省略すると、NULLが設定されます。WHERE condition: 更新対象のレコードを絞り込む条件式を指定します。
例
例1：顧客ステータスを更新する
顧客テーブル(customers)において、購入金額に基づいて顧客ステータスを更新する例です。
UPDATE customers
SET status =
CASE
WHEN purchase_amount >= 10000 THEN 'Gold'
WHEN purchase_amount >= 5000 THEN 'Silver'
ELSE 'Bronze'
END
WHERE 1;
例2：在庫数を更新し、在庫切れの場合はステータスを変更する
商品テーブル(products)において、在庫数を更新し、在庫切れの場合はステータスを変更する例です。
UPDATE products
SET stock_count = stock_count - quantity_sold,
status =
CASE
WHEN stock_count > 0 THEN 'In stock'
ELSE 'Out of stock'
END
WHERE 1;
注意点
CASE WHEN/THEN/ELSE構文は、条件に応じて異なる値を更新する機能を提供しますが、複雑な更新ロジックを記述する場合には、可読性を考慮する必要があります。- 複数の条件が互いに排他的でない場合、最初の真の条件のみが評価され、その後の条件は評価されません。
default_valueを省略すると、すべての条件が偽の場合にNULLが設定されます。必要に応じて、適切なデフォルト値を設定することをお勧めします。
この例では、顧客テーブル(customers)のデータを以下の条件に基づいて更新します。
- 1年以上の購買履歴がある顧客は、
vip_customerフラグをtrueに更新 - 購入金額が10万円以上の顧客は、
discount_rate列に割引率を設定
UPDATE customers
SET vip_customer =
CASE
WHEN DATEDIFF(CURDATE(), purchase_date) >= 365 THEN true
ELSE false
END,
discount_rate =
CASE
WHEN purchase_amount >= 100000 THEN 0.1
ELSE 0
END
WHERE 1;
例2：商品テーブルの在庫数を更新し、ステータスを変更
この例では、商品テーブル(products)の在庫数を更新し、以下の条件に基づいてステータスを変更します。
- 在庫数が10個以下になった商品は、ステータスを
Low stockに更新
UPDATE products
SET stock_count = stock_count - quantity_sold,
status =
CASE
WHEN stock_count <= 10 THEN 'Low stock'
WHEN stock_count = 0 THEN 'Out of stock'
ELSE 'In stock'
END
WHERE 1;
例3：注文ステータスを更新し、発送予定日を算出
この例では、注文テーブル(orders)のステータスを更新し、以下の条件に基づいて発送予定日を算出します。
- 支払いステータスが
paidの場合、ステータスをProcessingに更新し、発送予定日を5営業日後に設定 - 支払いステータスが
shippedの場合、ステータスをShippedに更新し、発送日を現在日に設定
UPDATE orders
SET status =
CASE
WHEN payment_status = 'paid' THEN 'Processing'
WHEN payment_status = 'shipped' THEN 'Shipped'
ELSE status
END,
estimated_shipping_date =
CASE
WHEN payment_status = 'paid' THEN DATE_ADD(CURDATE(), INTERVAL 5 DAY)
WHEN payment_status = 'shipped' THEN CURDATE()
ELSE estimated_shipping_date
END
WHERE 1;
CASE WHEN/THEN/ELSE 構文以外の代替方法
IF 関数
IF 関数は、条件式に基づいて真または偽の値を返す関数です。この値を更新対象の列に代入することで、条件に応じて異なる値を更新することができます。
UPDATE table_name
SET column_name = IF(condition, value1, value2)
WHERE condition;
例:
UPDATE customers
SET status = IF(purchase_amount >= 10000, 'Gold', 'Silver')
WHERE 1;
結合操作
複数のテーブルを結合し、条件に応じて必要な値を取得する方法です。この方法は、CASE WHEN/THEN/ELSE 構文よりも複雑になる場合がありますが、より柔軟な更新処理を実現することができます。
UPDATE customers
c
JOIN (
SELECT customer_id,
CASE
WHEN purchase_amount >= 10000 THEN 'Gold'
ELSE 'Silver'
END AS status
FROM orders
GROUP BY customer_id
) AS order_status
o
ON c.customer_id = o.customer_id
SET c.status = o.status;
サブクエリ
サブクエリを使用して、条件に応じて必要な値を取得する方法です。この方法は、複雑な更新処理をモジュール化できるという利点がありますが、可読性が低下する可能性があります。
UPDATE customers
c
SET c.status = (
SELECT
CASE
WHEN purchase_amount >= 10000 THEN 'Gold'
ELSE 'Silver'
END
FROM orders
WHERE c.customer_id = customer_id
LIMIT 1
);
ストアドプロシージャ
ストアドプロシージャは、再利用可能なデータベースモジュールです。複雑な更新ロジックをストアドプロシージャにカプセル化することで、可読性と保守性を向上させることができます。
CREATE PROCEDURE update_customer_status()
BEGIN
UPDATE customers
SET status =
CASE
WHEN purchase_amount >= 10000 THEN 'Gold'
ELSE 'Silver'
END
WHERE 1;
END;
CALL update_customer_status();
最適な方法の選択
どの方法が最適かは、更新対象のデータ量、更新ロジッ クの複雑さ、パフォーマンス要件などの状況によって異なります。
- シンプルな更新処理:
CASE WHEN/THEN/ELSE構文が簡潔で読みやすく、効率的です。 - 複雑な更新処理: 結合操作、サブクエリ、ストアドプロシージャなどの方法が、より柔軟でモジュール化された更新ロジックを実現できます。
- パフォーマンスが重要な場合: どの方法を選択する場合でも、インデックスを適切に使用し、クエリの最適化を検討する必要があります。
mysql
