Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6723

Allow derived table as a source in MERGE statement

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.11.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal

      Description

      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.
      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                Alex K. Aleksei Kovura
              • Votes:
                3 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated: