PostgreSQL syntax error at or near "SELECT"

Question

I have no idea why this error is appearing. The query was working fine and then I tried making it prettier and then this happens

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

Any help would be greatly appreciated

Or like this ? with "tempTable" as ( SELECT count(*) as "newVal", "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") ) UPDATE "topTenCategories" SET "membersCount" = "tempTable"."newVal" from "tempTable" WHERE "topTenCategories"."groupId" = "tempTable"."id"; — Philippe
– Philippe, Commented Jun 30, 2021 at 9:18
@Philippe this one works, thanks. But what is the issue in my query — LukeWarm2897
– LukeWarm2897, Commented Jun 30, 2021 at 9:20

user330315 · Accepted Answer · 2021-06-30 09:25:28Z

1

You can't put a SELECT into a values clause like that. The VALUES clause is intended for constant values and is not needed here.

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

You also need to remove the double quotes when you reference temptable as the quotes make it case sensitive and tempTable is a different name than "tempTable"

In general it's highly recommended to avoid those dreaded quoted identifiers to begin with.

answered Jun 30, 2021 at 9:25

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

LukeWarm2897 Over a year ago

thanks a lot. Sadly the system I am working started off with camelCase so I need to follow along.

Collectives™ on Stack Overflow

PostgreSQL syntax error at or near "SELECT"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related