mysql - Postgresql 查询用于在连接期间更新不同表中的多个列

Question

我们正在努力将数据从 MySQL 迁移到 PostgreSQL。

示例表结构:

                     Table "public.model"
       Column       |            Type             | Modifiers 
--------------------+-----------------------------+-----------
 model_id           | integer                     | not null
 last_purchase_time | timestamp without time zone | 
Indexes:
    "model_pkey" PRIMARY KEY, btree (model_id)
Referenced by:
    TABLE "items" CONSTRAINT "items_model_id_fkey" FOREIGN KEY (model_id) REFERENCES model(model_id)

                  Table "public.items"
    Column     |            Type             | Modifiers 
---------------+-----------------------------+-----------
 item_id       | integer                     | not null
 purchase_time | timestamp without time zone | 
 model_id      | integer                     | 
Indexes:
    "items_pkey" PRIMARY KEY, btree (item_id)
Foreign-key constraints:
    "items_model_id_fkey" FOREIGN KEY (model_id) REFERENCES model(model_id)
Referenced by:
    TABLE "invoice" CONSTRAINT "invoice_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)

                 Table "public.invoice"
    Column    |            Type             | Modifiers 
--------------+-----------------------------+-----------
 inovoice_id  | integer                     | not null
 invoice_time | timestamp without time zone | 
 item_id      | integer                     | 
Indexes:
    "invoice_pkey" PRIMARY KEY, btree (inovoice_id)
Foreign-key constraints:
    "invoice_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)

如果我们必须使用连接更新单个表的列，那么我们可以在 postgresql 中使用以下查询。

update model
   set last_purchase_time = now()
from items
     join invoice
        on items.item_id = invoice.item_id
where model.model_id = items.model_id
  and invoice.item_id = 2002;

下面是一个更新查询，用于更新所有表中的多个列，在 MySQL 中运行良好。

update model
       join items
          on model.model_id = items.model_id
       join invoice
          on items.item_id = invoice.item_id
set model.last_purchase_time = now(),
    items.purchase_time = now(),
    invoice.invoice_time = now()
where invoice.item_id = 2002;

有人可以建议一个等效的 PostgreSQL 查询来使用 join 更新不同表中的多个列吗？

Answer 1

你可以在 PostgreSQL 中这样做:

WITH upd1 AS
        (UPDATE invoice
         SET invoice_time = current_timestamp
         FROM items
            JOIN model
               ON model.model_id = items.model_id
         WHERE items.item_id = invoice.item_id
            AND invoice.item_id = 2002
         RETURNING model.model_id, items.item_id),
     upd2 AS
        (UPDATE items
         SET purchase_time = current_timestamp
         FROM upd1
         WHERE upd1.item_id = items.item_id)
UPDATE model
SET last_purchase_time = current_timestamp
FROM upd1
WHERE upd1.model_id = model.model_id;

唯一的缺点是 items 和 model 的更新频率会超出必要。