CTE(WITH句)を使ったUPDATE

現状

PostgreSQLでUPDATEでCTEを使おうとした瞬間、こんなエラーが出てきた。

WITH MyCTE AS (
    SELECT id FROM ...
)
UPDATE RealTable 
SET 
    target_column = '1'
FROM 
    RealTable join MyCTE AS cte 
on
    RealTable.id = cte.id; 

ERROR: syntax error at or near "UPDATE"

構文エラーって何か調べてみたら。

理由

CTEを使ったUPDATE構文はDBごとにバラバラだった。
別のテーブル（CTE）のデータを使って、既存のテーブルを更新するという操作は、SQLの標準規格で定義されていないため、各DBベンダーが独自の構文を実装している。

1. PostgreSQL (暗黙的JOIN)

PostgreSQLは、UPDATE文のFROM句にCTE（データソース）を指定し、WHERE句で更新対象テーブルとCTEを「暗黙的にJOIN」する。

WITH MyCTE AS (
    SELECT id FROM ...
)
UPDATE RealTable  -- ① 更新対象テーブル
SET 
    target_column = '1'
FROM 
    MyCTE AS cte  -- ② データソース（CTE）
WHERE 
    RealTable.id = cte.id; -- ③ JOIN条件

ドキュメント

[ FROM from_list ] のセクションを読んでみるとUPDATE対象のテーブル（RealTable）はFROM句に記載できなのでエラーが出たのだった。

2. SQL Server (T-SQL) (明示的JOIN)

SQL Serverは、FROM句に更新対象のテーブル自体も書き、JOINキーワードを使って明示的にCTEと結合できる。

WITH MyCTE AS (
    SELECT id FROM ...
)
UPDATE RealTable  -- ① 更新対象テーブル
SET 
    target_column = '1'
FROM 
    RealTable  -- ② 更新対象テーブルを再度指定
JOIN 
    MyCTE AS cte ON RealTable.id = cte.id; -- ③ JOIN条件

ドキュメント

3. Oracle (MERGE文)

Oracleは、UPDATE JOIN専用の構文を持たず、代わりにMERGE文を使用するのが一般的。
MERGEは「（条件に）一致したらUPDATE、一致しなかったらINSERT」という操作を一度に実行する。
WHEN MATCHEDのみ記載して、WHEN NOT MATCHEDを記載しないと、UPDATEのみできる。

MERGE INTO RealTable t  -- ① 更新対象テーブル
USING (SELECT id FROM ...) cte         -- ② データソース（CTE）
ON (t.id = cte.id)      -- ③ JOIN条件
WHEN MATCHED THEN
    UPDATE SET t.target_column = '1' -- ④ 一致した時のUPDATE処理

ドキュメント

まとめ

CTE UPDATEは標準がなく、DBが変われば構文も変わる。