Update From Select with Correlated and Join in PostgreSQL

Question

我正在从SQL Server迁移到Postgres，这在很大程度上还不错。其中一个问题是，我无法弄清楚如何在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"

我怎么才能让它工作呢？

Answer 1

您可以对此使用DISTINCT ON来简化它。

子查询中的ORDER BY将确保它是最小“索引”的"StrataId“。

UPDATE "Measure" m
SET "DefaultStrataId" = q."StrataId"
FROM
(
  SELECT DISTINCT ON (s."MeasureId") s."MeasureId", s."index", s."StrataId"
  FROM "Strata" s
  JOIN "Point" p ON p."StrataId" = s."StrataId"
  JOIN "Measure" m ON m."MeasureId" = s."MeasureId"
  ORDER BY s."MeasureId", s."index"
) q
WHERE q."MeasureId" = m."MeasureId";

在db<>fiddle 上测试

顺便说一句，下面的查询也适用于该小提琴中的测试数据。

基本上，在Postgresql中，引用名称与不引用名称是不同的。

在这方面，MS Sql Server的容忍度要高得多。

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";

Answer 2

很时髦。在被@wildplasser羞辱之后，我的查询变得不那么令人反感，一切都神奇地开始工作了。除了切换到标准连接和添加别名之外，我没有做任何更改：

update
    "Measure" m set
        "DefaultStrataId" = "StrataId"
    from
        (
        select
            s."MeasureId",
            min(s."Index") as "Index"
        from
            "Measure" m
        inner join "Strata" s on
            s."MeasureId" = m."MeasureId"
        where s."StrataId" in (
            select
                s."StrataId"
            from
                "Point")
        group by
            s."MeasureId") a
    inner join "Strata" s on
        s."Index" = a."Index"
    where
        s."MeasureId" = m."MeasureId";

Answer 3

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

省略所有丑陋的引号，并添加一些别名(假设只有一条具有最小值的记录)：

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"  
        )
        ;