サブクエリを使ってデータを更新するSQLの書き方～update～

更新条件にサブクエリを使ってデータ(レコード)を更新するSQLの書き方について紹介していきます

前提条件

商品ごとの販売個数を管理するorder_ditailsテーブルと商品金額を管理するproductsテーブルを使用します

使用するテーブル：

order_ditailsテーブル

+------+----------+-------------+----------+
| id   | quantity | order_price | order_id |
+------+----------+-------------+----------+
|    1 |        3 |        6000 |        2 |
|    2 |        2 |        2000 |        1 |
|    3 |        2 |        3000 |        3 |
|    4 |        1 |        2500 |        5 |
|    5 |        4 |        6000 |        6 |
|    6 |        3 |        6000 |        2 |
+------+----------+-------------+----------+

prductsテーブル

+----+-----------------+-------+
| id | name            | price |
+----+-----------------+-------+
|  1 | ドックフード001 |  1000 |
|  2 | ドックフード002 |  2000 |
|  3 | ドックフード003 |  1500 |
|  4 | ドックフード004 |  1800 |
|  5 | ドックフード005 |  2500 |
|  6 | ドックフード006 |  1500 |
|  7 | ドックフード007 |  4000 |
|  8 | ドックフード008 |  6500 |
|  9 | ドックフード009 |  3000 |
| 10 | ドックフード010 |  5000 |
+----+-----------------+-------+

条件：

• 商品の販売個数が3個以上を抽出して、商品金額を20％UPさせる
• order_detailsテーブルのorder_idとproductsテーブルのidは紐づいている

update文の基本は下記を参照

update対象となるデータを確認する

構文

select sum(カラム名) from テーブル名
group by order_id having sum(quantity)>=3;

コードの見方

今回のアップデート対象となる商品を確認するため order_details のレコードのうち3個以上の販売数がある商品を抽出する

使用SQL文

select order_id,sum(quantity) from order_details 
group by order_id having sum(quantity)>=3;

出力結果

+----------+---------------+
| order_id | sum(quantity) |
+----------+---------------+
|        2 |             6 |
|        6 |             4 |
+----------+---------------+
2 rows in set (0.027 sec)

今回、販売価格を20％UPさせる商品はid2と6の商品であることがわかりました

サブクエリを利用してupdate文を実行する

構文

select sum(カラム名) from テーブル名
group by order_id having sum(quantity)>=3;

使用するSQL文

update products set price = price * 1.2 
where id in(select order_id from order_details 
group by order_id having sum(quantity)>=3);

コードを実行したら更新内容を確認する

+----+-----------------+-------+
| id | name            | price |
+----+-----------------+-------+
|  1 | ドックフード001 |  1000 |
|  2 | ドックフード002 |  2400 |
|  3 | ドックフード003 |  1500 |
|  4 | ドックフード004 |  1800 |
|  5 | ドックフード005 |  2500 |
|  6 | ドックフード006 |  1800 |
|  7 | ドックフード007 |  4000 |
|  8 | ドックフード008 |  6500 |
|  9 | ドックフード009 |  3000 |
| 10 | ドックフード010 |  5000 |
+----+-----------------+-------+
10 rows in set (0.000 sec)

• id2 更新前　2000円　⇒　更新後　2400円
• id6 更新前　1500円　⇒　更新後　1800円

累計販売数が3個以上あるid2と6の商品の価格を20％UPさせることができました

サブクエリの詳細は下記を参照