SQLのwith句を使ったupdateについてまとめています。
SQLのwith～updateの文法
with句はサブクエリを簡潔に記述することができます。
【関連記事】
▶SQLのwith句のサンプル サブクエリを共通テーブル式（CTE）を使って、単純なクエリで記述
以下は、MySQLにて、employees（社員）テーブルの値を取得するサブクエリをwithで定義して、departmetns2（部署）テーブルを更新する例です。
WITH val
AS (SELECT first_name
FROM employees
WHERE emp_no = "10001")
UPDATE departments2
SET dept_name = (SELECT * FROM val)
WHERE dept_no = "d001";
mysql> select * from departments2;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d001 | Georgi |
| d003 | Human Resources |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
9 rows in set (0.00 sec)
社員番号10001の社員のfirst_name（姓名の名）、「Georgi」を、部署テーブルの部署番号d001の値としてupdateしています。更新後のselect文で、更新内容を確認しています。
Oracleのwithを使ったupdate文
UPDATE TESTTBL
SET STR1 = '1',NUM1 = 1
WHERE
STR1 =
(
WITH A AS
(
SELECT '1' AS STR1
, 1 AS NUM1
FROM DUAL
)
SELECT STR1
FROM A
)
参考）社内SEの徒然なる日記 SQL（Oracle）のUPDATE文でWITH句を使う！
Oracleでも、MySQLのようにwithを使ってupdate文を記述することが可能です。上記の例のように、サブクエリ部分にwithを記述することもできますが、この使い方だとwithを使うメリットがあまりないかも知れません。
Oracleでは、他のDBMSでは動作するコードも、記述方法によってはSyntax Errorとなるケースがあるようです。
SQL Serverのwithを使ったupdate文
USE tempdb; GO -- UPDATE statement with CTE references that are correctly matched. DECLARE @x TABLE (ID INT, Value INT); DECLARE @y TABLE (ID INT, Value INT); INSERT @x VALUES (1, 10), (2, 20); INSERT @y VALUES (1, 100),(2, 200); WITH cte AS (SELECT * FROM @x) UPDATE x -- cte is referenced by the alias. SET Value = y.Value FROM cte AS x -- cte is assigned an alias. INNER JOIN @y AS y ON y.ID = x.ID; SELECT * FROM @x; GO ID Value ------ ----- 1 100 2 200 (2 row(s) affected)
関連）UPDATE (Transact-SQL) – SQL Server | Microsoft Docs
上記は、テーブル@xの値を、結合した別テーブル＠yの値で更新する例です。withで定義した共通テーブル式に別名定義（cte AS x）を使う場合、共通テーブル式の記述場所全てに別名を使う必要があります。
MySQLのwithを使ったupdate文
WITH val
AS (SELECT first_name
FROM employees
WHERE emp_no = "10001")
UPDATE departments2
SET dept_name = (SELECT *
FROM val)
WHERE dept_no = "d001";
WITHを使ったupdate文は、サブクエリに書き直すことが可能です。以下は、setの値指定部分にサブクエリを使った例です。
update departments2 set dept_name=(select first_name from employees where emp_no="10001") where dept_no="d001";
updateのテーブル指定箇所でサブクエリを使う場合は以下のようになります。
mysql> update departments2, (select first_name from employees where emp_no="10001") tbl2 set dept_name=tbl2.first_name where dept_no="d001";
【関連記事】
▶MySQLのupdateでselectした結果を使う方法 サブクエリで自己テーブル更新も可能
いずれも結果は同じで、departmets2（部署）テーブルのd001のdept_name（部署名）が、employees（社員）テーブルのemp_no（社員番号）10001のfirst_name（姓名の名）、「Georgi」で更新されます。
select * from departments2 order by dept_no; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d001 | Georgi | | d002 | Finance | | d003 | Human Resources | | d004 | Production | | d005 | Development | | d006 | Quality Management | | d007 | Sales | | d008 | Research | | d009 | Customer Service | +---------+--------------------+
PostgreSQLのwithを使ったupdate文
with内にupdate文を記述する場合、以下の用に記述することで「updateで更新前の値」と「updateで更新後の値」を取得することができます。
以下のSQLは、updateで更新前の値をselect文で取得します。
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;
以下のSQLは、updateで更新後の値をselect文で取得します。
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM t;
参考）PostgreSQLの公式ドキュメント：WITH問い合わせ（共通テーブル式）
まとめ
- withによる共通テーブル式をupdate文と組み合わせて使える
- OracleなどDBMSによって、withを使った記述に制約があるケースあり
- postgresqlでは、withを使ってupdate実行前の値と実行後の値を取得できる
