db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aleksej Kovura (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-6723) Allow derived table as a source in MERGE statement
Date Sun, 31 Aug 2014 09:55:52 GMT
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:
            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

View raw message