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

当您更新CTE时，背景表也会随之更新。CTE下面只能有一条语句。发布CTE将失去其作用域。您只能执行UPDATE语句，post CTE。我已经修改了CTE并更新了前1行。

以上语句适用于SQL Server。在Postgres中，CTE不能作为UPDATE语句的目标。请参阅Postgres中的以下错误。

查询错误:错误:关系"cte“不能作为修改语句的目标

WITH cte AS (
select top 1
    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 mtstatus_name = true

我已经尝试使用样本数据进行CTE更新。下面的代码在SQL Server中运行良好。

create table #test(a int)
create table #test2(a int, b int)

insert into #test values (1)
insert into #test2 values (1,1)

;WITH CTE as
(
select top 1 t.a, t2.b
FROM #test as t
join #test2 as t2
on t.a = t2.a
order by t.a desc
)
update cte set b = 0 

select * from #test2

Answer 2

您可以尝试使用具有Postgres语法的update join，包括用于查询的限制部分的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';