Zenn
🐘

[Postgres]WITHした結果をUPDATEする

2024/11/24に公開
PostgreSQL
tech

はじめに

postgresではUPDATEにFROMを使うことで、他テーブルやサブクエリの結果を指定したテーブルへ更新することができます。
今回はWITHを使ったサブクエリの結果をテーブルへ更新する処理をしていきたいと思います🙌

やること

下記の商品テーブル(product_table)から表示状態が表示中のものを商品IDの昇順で連番を振っていきます！

product_id
(商品ID)		 product_name
(商品名)		 status
(表示状態)		 displaysort
(表示順)
1 Tシャツ 00 null
2 ジーンズ 00 null
3 スカート 01 null
4 パーカー 00 null
5 セーター 01 null

WITH

まず、statusが表示中で商品IDの昇順で連番した結果を取得していきます。

WITH 
    product_index AS
    (
    SELECT 
        product_id ,
        product_name ,
        status ,
        displaysort ,
        row_number() OVER(ORDER BY product_id) AS index_valid
    FROM 
        public.product_table
    WHERE
        status ='00' 
    )
商品ID
(product_id)		 商品名
(product_name)		 表示状態
(status)		 表示順
(displaysort)		 連番
(index_valid)
1 Tシャツ 00 NULL 1
2 ジーンズ 00 NULL 2
4 パーカー 00 NULL 3

UPDATE

FROMにWITHの結果を入れ、商品IDとWITHの商品IDが一致しているものに、displaysortへWITHの連番結果を更新していきます。

UPDATE 
    public.product_table
SET 
    displaysort = product_index.sort_valid
FROM
    product_index
WHERE
    product_table.product_id = product_index.product_id;
product_id product_name status displaysort
1 Tシャツ 00 1
2 ジーンズ 00 2
3 スカート 01 NULL
4 パーカー 00 3
5 セーター 00 NULL

おわりに

UPDATEにFROMがあるのを知らず、たまたま見つけたのでまとめてみました。
FROMを活用することで、より柔軟なデータ更新が可能になるため、今後の開発でも積極的に使用していきたいです！👌

Discussion