时间戳

首页 > 解决方案 > “SELECT”处或附近的 PostgreSQL 语法错误

postgresql - “SELECT”处或附近的 PostgreSQL 语法错误

问题描述

我不知道为什么会出现这个错误。查询工作正常，然后我尝试使它更漂亮，然后发生这种情况

UPDATE "topTenCategories" SET "membersCount" =  "tempTable"."newVal" FROM 
    ( 
        VALUES 
        (
            SELECT count(*), "g"."id" FROM 
            "groups" AS "g" 
            LEFT JOIN 
            "groupMembers" As "gm" 
            ON "g"."id" = "gm"."groupId" 
            WHERE "g"."isCategory" is true and "g"."parentCategoryId" is null group by ("g"."id")
        )
    ) AS tempTable ("newVal", "id")
    WHERE "topTenCategories"."groupId" = "tempTable"."id";

ERROR:  syntax error at or near "SELECT"
LINE 5:                 SELECT count(*), "g"."id" FROM 
                        ^
SQL state: 42601
Character: 137

任何帮助将不胜感激

标签： postgresqlpostgresql-13

解决方案

您不能将 aSELECT放入这样的values子句中。该VALUES子句用于常量值，此处不需要。

UPDATE "topTenCategories" 
    SET "membersCount" =  tempTable."newVal" 
FROM ( 
  SELECT count(*), "g"."id" 
  FROM "groups" AS "g" 
    LEFT JOIN "groupMembers" As "gm" ON "g"."id" = "gm"."groupId" 
  WHERE "g"."isCategory" is true 
    and "g"."parentCategoryId" is null group by ("g"."id")
) AS tempTable("newVal", "id")
WHERE "topTenCategories"."groupId" = tempTable."id";

您还需要在引用时删除双引号，temptable因为引号使其区分大小写并且tempTable名称不同于"tempTable"

一般来说，强烈建议一开始就避免使用那些可怕的带引号的标识符。

推荐阅读