sql-server - Postgresql - 从 CTE 输出更新 CTE 结果？

Question

我需要根据最新的时间戳更新 CTE o/p 列值之一(前 1 条记录)，然后返回。

查询

WITH cte AS (
select
    dt_zone.zone_name,
    dt_material_status.mtstatus_name,
    dt_historicalzone.visit_time_in
FROM ((public.dt_historicalzone 
    INNER JOIN dt_material_status
        ON dt_historicalzone.mtstatus_id = dt_material_status.mtstatus_id)
    INNER JOIN dt_zone ON dt_historicalzone.zone_id = dt_zone.zone_id)
    WHERE material_id = 'ELS46885' 
    ORDER BY dt_historicalzone.zone_id DESC)

UPDATE cte SET cte.mtstatus_name = true WHERE SELECT * FROM cte LIMIT 1;
SELECT * FROM cte

Answer 1

您可以尝试使用更新连接，使用 Postgres 的语法，包括查询限制部分的 CTE:

WITH cte AS (
    SELECT dh.mtstatus_id
    FROM dt_historicalzone dh
    INNER JOIN dt_zone dz
        ON dh.zone_id = dz.zone_id
    ORDER BY zone_id DESC
    LIMIT 1
)

UPDATE dt_material_status d
SET mtstatus_name = true
FROM cte t
WHERE d.mtstatus_id = t.mtstatus_id AND
      d.material_id = 'ELS46885';