如何在SQL UPDATE后返回带WITH子句与多关联的复杂查询结果

需求说明 #

执行SQL UPDATE操作后，需要返回包含WITH子句、多表关联的完整查询结果，而非仅返回被更新的字段。

现有查询与问题 #

原关联查询语句（用于获取目标数据结构）：

WITH orders AS (
  SELECT product_id, SUM(amount) AS orders
  FROM orders_summary
  GROUP BY product_id
)
SELECT p.id, p.name,
p.date_of_creation,
o.orders, s.id AS store_id,
s.name AS store_name
FROM products AS p
LEFT JOIN orders AS o
ON p.id = o.product_id
LEFT JOIN stores AS s
ON s.id = p.store_id
WHERE p.id = '1'

原UPDATE语句无法实现需求（RETURNING无法直接返回复杂关联结果）：

UPDATE products
SET name = 'ABC'
WHERE id = '1'
RETURNING up_query

期望结果 #

解决方案 #

通过将UPDATE嵌入CTE，再关联其他表完成复杂查询，即可返回更新后的完整结果：

WITH updated_product AS (
  UPDATE products
  SET name = 'ABC'
  WHERE id = '1'
  RETURNING id, name, date_of_creation, store_id
),
orders AS (
  SELECT product_id, SUM(amount) AS orders
  FROM orders_summary
  GROUP BY product_id
)
SELECT 
  up.id, 
  up.name, 
  up.date_of_creation AS date,
  o.orders, 
  s.id AS store_id, 
  s.name AS store_name
FROM updated_product AS up
LEFT JOIN orders AS o ON up.id = o.product_id
LEFT JOIN stores AS s ON up.store_id = s.id;

逻辑说明 #

1. updated_product CTE执行UPDATE操作，同时返回后续关联需要的products表字段；
2. orders CTE保留原查询中的订单统计逻辑；
3. 主查询将更新后的产品数据与订单统计、店铺表关联，输出符合预期的完整结果。

内容的提问来源于stack exchange，提问作者Mj Ebrahimzadeh