从使用相关子查询选择更新，并在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";

在这里测试数据库<>小提琴

顺便说一句，下面的查询也适用于该小提琴中的测试数据。
基本上，在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

Funky。在为@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"  
        )
        ;