Amazon Redshift syntax error when using UPDATE on specified VALUEs

Question

This is the query that I'm trying to run to update multiple rows by their ID in one single string. I'm using AWS Redshift (which is based on a very old version of PostgreSQL):

UPDATE promotion
   SET engagements = c.engagements
  FROM (VALUES (668, 73), (684, 135), (680, 73), (672, 76), (682, 94),
               (676, 4), (670, 81), (686, 104), (678, 1), (674, 0)
       ) AS c(id, engagements)
 WHERE c.id = promotion.id;

And this is the error being produced:

sqlalchemy.exc.ProgrammingError: (pg8000.core.ProgrammingError) (u'ERROR', 
u'42601', u'syntax error at or near ","', u'72', u'/home/ec2-
user/padb/src/pg/src/backend/parser/parser_scan.l', u'699', u'yyerror', u'', 
u'')
[SQL: 'UPDATE promotion SET engagements = c.engagements FROM (VALUES 
(668, 73), (684, 135), (680, 73), (672, 76), (682, 94), (676, 4), (670, 81), 
(686, 104), (678, 1), (674, 0)) AS c(id, engagements) WHERE c.id = 
promotion.id;']

EDIT: Using query format from answer here: Update multiple rows in same query using PostgreSQL

This is just a stab into the dark from looking at other's code online, but isn't the format usually to put VALUES on the outside of the enclosing parenthesis? : FROM VALUES ((668, 73), (684, 135)... etc. — jarcobi889, Feb 22 '17 at 21:00
From [unsupported Postgres features](http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html): "*VALUES list used as constant tables*" — , Feb 22 '17 at 21:02
@jarcobi889 I got it from here: http://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql#18799497 — jstudios, Feb 22 '17 at 21:02
@a_horse_with_no_name Could you explain better? this might be the answer. — jstudios, Feb 22 '17 at 21:05
That syntax is simply not supported in Redshift. The answer you are referring to is for Postgres, not for Redshift — , Feb 22 '17 at 21:06
Still, Redshift seems to support [`executemany` when you pass a list of dicts](http://docs.sqlalchemy.org/en/latest/core/tutorial.html#inserts-updates-and-deletes): `.execute(table.insert(), [{...},...])` — 9000, Feb 22 '17 at 21:08

score 0 · Answer 1 · answered Feb 23 '17 at 15:14

Redshift supports VALUES synthax only for inserts. However, if you really want to use something like this in a different context you can construct a statement with UNION:

UPDATE promotion
SET engagements = c.engagements
FROM (
    SELECT 668 as id, 73 as engagements
    UNION SELECT 684, 135
    UNION SELECT 680, 73
    -- to be continued
) c
WHERE c.id = promotion.id;

but I would better insert the records into a table and then UPDATE FROM that table

Amazon Redshift syntax error when using UPDATE on specified VALUEs

1 Answers1