Affects Version/s: 10.11.1.1
Fix Version/s: None
I'm trying to utilize MERGE to do an upsert operation like this:
MERGE INTO "APP"."ST_KEY_VAL" trg USING (SELECT * FROM (VALUES ('key1', 'val1')) as t1 (st_key, st_val)) as src
ON trg.st_key = src.st_key
WHEN MATCHED THEN UPDATE SET st_val = src.st_val
WHEN NOT MATCHED THEN INSERT VALUES (src.st_key, src.st_val)
Derby returns an error:
Error: The source table of a MERGE statement must be a base table or table function.
MERGE is used this way on other databases, unfortunately I can't find whether it is standard compliant or not. If this gets implemented, I would use it in PreparedStatement, replacing values with "?"-s.