Combining Db2 from jdbc with dynamic values

Question

I would like to use the db2 merge operator, representing it as an instruction from jdbc. I mean the following scenario. I work with a proprietary persistence level and I process the entity, I don’t know if it has already been saved or not, and I would like to use the merge operator to insert or update a row in the database. Is it possible? Suppose I work with a table with three columns: id, name, surname, and I process the object with id = "5", name = "chuck", surname = "norris". Can i release:

MERGE INTO people AS t USING (select '5' as id, 'chuck' as name, 'norris' as surname from SYSIBM.SYSDUMMY1)As s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.name=s.name, t.surmane=s.surname WHEN NOT MATCHED THEN INSERT (id, name, surname) VALUES (s.id, s.name, s.surname) 

such a statement? I am trying to do this, but I have a mistake. I don't think this is allowed to use select after USING:

 USING (select '5' as id, 'chuck' as name, 'norris' as surname from SYSIBM.SYSDUMMY1)As s 

I also tried to do:

 USING VALUES('5','chuck','norris') AS s(id,chuck,norris) 

but it does not work. Any help would be greatly appreciated. In addition, does anyone know if such an expression can be used in a prepared statement by replacing the real values ​​expressed in the USING part with "?" placeholders to set them into a prepared statement using setXXX () methods?

thanks

Thanks Fil

Answer 1

The MERGE syntax for your data will be something like this if you are using DB2 Linux / Unix / Windows (LUW). The VALUES clause is included in the parenthesis for the USING part.

Also, if you use LUW, you cannot dynamically prepare MERGE (IE, your request cannot have parameter markers) in LUW 9.5 or less. This was added in LUW 9.7.

 MERGE INTO people AS t USING ( VALUES (5, 'Chuck', 'Norris'), (6, 'John', 'Smith'), (7, 'Abraham', 'Lincoln') -- maybe more rows ) AS s (id, name, surname) ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.name=s.name, t.surname=s.surname WHEN NOT MATCHED THEN INSERT (id, name, surname) VALUES (s.id, s.name, s.surname) 

However, your actual problem with the full choice may be that you have typos in your query ... for example, "surmane" in UPDATE SET t.name=s.name, t.surmane=s.surname