1

I am trying to do an UPSERT in DB2 9.7 without creating a temporary table to merge. I am specifying values as parameters, however I'm always getting a syntax error for the comma separating the values when I try to include more than one row of values.

MERGE INTO table_name AS tab 
USING (VALUES 
        (?,?), 
        (?,?)
    ) AS merge (COL1, COL2) 
    ON tab.COL1 = merge.COL1 
    WHEN MATCHED THEN 
        UPDATE SET tab.COL1 = merge.COL1, 
                   tab.COL2 = merge.COL2
    WHEN NOT MATCHED THEN 
        INSERT (COL1, COL2) 
        VALUES (merge.COL1, merge.COL2)

I have also tried teknopaul's answer from Does DB2 have an “insert or update” statement, but have received another syntax error complaining about the use of SELECT.

Does anybody know how to correctly include a table with values in my merge, without actually creating/dropping one on the database?

Community
  • 1
  • 1
chararray
  • 79
  • 1
  • 12
  • this could help you https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en – Mustafa DOGRU Jul 19 '16 at 06:43
  • Are you always filling out all rows? Or only a subset? – Clockwork-Muse Jul 19 '16 at 11:56
  • @Clockwork-Muse Yes all rows. I want it to update records if they are already there, and insert them if they are not. – chararray Jul 19 '16 at 12:50
  • Don't use a reserved word for the correlation name: `AS merge (COL1, COL2)`. – mustaccio Jul 19 '16 at 14:55
  • @mustaccio Good point...however I'm still receiving the same error: `ERROR [42601] [IBM][CLI Driver][DB2] SQL0104N An unexpected token "," was found following "". Expected tokens may include: "FOR )". SQLSTATE=42601` – chararray Jul 19 '16 at 15:32
  • I don't know, this works just fine in version 10.5. Some syntax restrictions might have been relaxed since 9.7. Try adding `TABLE`: `USING TABLE (VALUES ... ) AS m (COL1, COL2)` – mustaccio Jul 19 '16 at 17:15
  • @mustaccio `ERROR [42601] [IBM][CLI Driver][DB2] SQL0199N The use of the reserved word "TABLE" following "" is not valid. Expected tokens may include: "(". SQLSTATE=42601` The original code above works on a different server. The difference is it's not on a mainframe... – chararray Jul 19 '16 at 18:54
  • Which version/platform are you on? There's no DB2 version 9.7 for z/OS. – mustaccio Jul 19 '16 at 18:57
  • @mustaccio DB2 8.2.1 I belive – chararray Jul 19 '16 at 19:07
  • Neither DB2 8 for z/OS, nor DB2 8.x for LUW supported the `MERGE` statement. May be you need to figure out what version on what platform you are dealing with before wasting any more time. You stated 9.7 (assuming LUW) in the question, then said it is "8.2.1" on mainframe in the comments. – mustaccio Jul 19 '16 at 19:36

1 Answers1

-2

I believe you need something like USING (SELECT * FROM VALUES ( ...) ) AS ...

Alexan
  • 8,165
  • 14
  • 74
  • 101
user10552590
  • 1