CODEWITHSUNDEEP
postgresqlsql-updateaggregate-functions
Teejay
Teejay

Reputation: 7481

PostgreSql: cannot use aggregate function in UPDATE

I have an Oracle query that I ported to PostgreSql:

UPDATE "SPD_PG"."TT_SPLDR_11A2F324_29" 
SET "SECT_ORDER" = MAX("SECTIONS"."SECT_ORDER")+1 FROM "SPD_PG"."SECTIONS"
INNER JOIN "SPD_PG"."META_SECTIONS" ON ("SECTIONS"."META_SECT_ID"="META_SECTIONS"."META_SECT_ID")
WHERE ("META_SECTIONS"."META_SECT_ORDER"="TT_SPLDR_11A2F324_29"."META_SECT_ORDER"-1)
AND ("SECTIONS"."DOC_ID"="TT_SPLDR_11A2F324_29"."DOC_ID")
AND ("TT_SPLDR_11A2F324_29"."META_SECT_ORDER">0)

This give me: ERROR: cannot use aggregate function in UPDATE, seems PostgreSql doesn't support MAX in Update statements.

However if I rewrite the query as follows:

UPDATE "SPD_PG"."TT_SPLDR_11A2F324_29" 
SET "SECT_ORDER" = "MAX_VALUE" FROM (
  SELECT MAX("SECTIONS"."SECT_ORDER")+1 AS "MAX_VALUE" FROM "SPD_PG"."SECTIONS"
  INNER JOIN "SPD_PG"."META_SECTIONS" ON ("SECTIONS"."META_SECT_ID"="META_SECTIONS"."META_SECT_ID")
  WHERE ("META_SECTIONS"."META_SECT_ORDER"="TT_SPLDR_11A2F324_29"."META_SECT_ORDER"-1)
  AND ("SECTIONS"."DOC_ID"="TT_SPLDR_11A2F324_29"."DOC_ID")
  AND ("TT_SPLDR_11A2F324_29"."META_SECT_ORDER">0)
) "TBL_ALIAS"

it says ERROR: subquery in FROM cannot refer to other relations of same query level.

So I can't figure out how to write this query.

Upvotes: 2

Views: 4913

Answers (1)

neshkeev
neshkeev

Reputation: 6476

Try this:

UPDATE "SPD_PG"."TT_SPLDR_11A2F324_29" 
   SET "SECT_ORDER" = (SELECT MAX("SECTIONS"."SECT_ORDER")+1 
                         FROM "SPD_PG"."SECTIONS"
                        INNER JOIN "SPD_PG"."META_SECTIONS" ON ("SECTIONS"."META_SECT_ID"="META_SECTIONS"."META_SECT_ID")
                        WHERE ("META_SECTIONS"."META_SECT_ORDER"="TT_SPLDR_11A2F324_29"."META_SECT_ORDER"-1)
                          AND ("SECTIONS"."DOC_ID"="TT_SPLDR_11A2F324_29"."DOC_ID")
                          AND ("TT_SPLDR_11A2F324_29"."META_SECT_ORDER">0)
                      )

Upvotes: 2

Related Questions