使用关联子查询从Select中更新并加入PostgreSQL [英] Update From Select with Correlated Subquery and Join in PostgreSQL

问题描述

我正在从SQL Server迁移到Postgres，并且在大多数情况下一切正常.问题之一是我无法弄清楚如何使此查询在Postgres中工作:

I am migrating from SQL Server to Postgres and it has gone okay for the most part. One of the issues is that I am unable to figure out how to make this query work in Postgres:

update
    "Measure"
set 
    DefaultStrataId = StrataId

FROM (SELECT "Strata"."MeasureId",
               Min("Strata"."index") AS "Index"
        FROM   "Strata",
               "Measure"
        WHERE  "Strata"."MeasureId" = "Measure"."MeasureId" and "Strata"."StrataId" in (select strataid from point)
        GROUP  BY "Strata"."MeasureId") a
       INNER JOIN strata 
               ON "Strata"."index" = a."index"
               where "Strata"."MeasureId" = "Measure"."MeasureId";  

它抱怨:SQL Error [42601]: ERROR: syntax error at or near "FROM"

如何使它工作?

推荐答案

您唯一的目标似乎是从Strata获得最低价值

Your only goal appears to be getting the minimal value from Strata

忽略所有丑陋的引号，并添加一些别名(假设仅存在一个带有最小值的记录):

Omitting all the ugly quotes,and adding some aliasses (assuming that only one record with the minumum value exists) :

UPDATE Measure m
SET    DefaultStrataId = s.StrataId
FROM   Strata s
WHERE  s.MeasureId = m.MeasureId
AND NOT EXISTS (
        SELECT * FROM Strata nx
        where nx.MeasureId = s.MeasureId  
        AND nx."index" < s."index"  
        )
        ;

这篇关于使用关联子查询从Select中更新并加入PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！