Aleksej Kovura created DERBY-6723:
-------------------------------------
Summary: Allow derived table as a source in MERGE statement
Key: DERBY-6723
URL: https://issues.apache.org/jira/browse/DERBY-6723
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.11.1.1
Reporter: Aleksej Kovura
Priority: Minor
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 st.state_key = src.state_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.
SQLState: 42XAL
ErrorCode: 30000
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.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
