SQLでwith句とupdateを使う方法 サブクエリを共通テーブル式で置き換えるサンプルコード

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実行前の値と実行後の値を取得できる