0

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

LukeWarm2897
  • 149
  • 3
  • 14
  • 2
    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 Jun 30 '21 at 09:18
  • @Philippe this one works, thanks. But what is the issue in my query – LukeWarm2897 Jun 30 '21 at 09:20

1 Answers1

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.