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

Support for SQL:2003 MERGE statement

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.11.1.1
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Patch Available

      Description

      A relatively common piece of logic in a database application is to check for a row's existence and then either update or insert depending on its existence.

      SQL:2003 added a MERGE statement to perform this operation. It looks like this:

      MERGE INTO table_name USING table_name ON (condition)
      WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
      WHEN NOT MATCHED THEN INSERT column1 [, column2 ...] VALUES (value1 [, value2 ...])

      At the moment, the only workaround for this would be to write a stored procedure to do the same operation, or to implement the logic client-side.

        Attachments

        1. derby-3155-01-ac-grammar.diff
          59 kB
          Rick Hillegas
        2. derby-3155-02-ag-fixParserWarning.diff
          0.6 kB
          Rick Hillegas
        3. derby-3155-03-ae-backingStoreHashtableWithRowLocation.diff
          37 kB
          Rick Hillegas
        4. derby-3155-03-af-backingStoreHashtableWithRowLocation.diff
          47 kB
          Rick Hillegas
        5. derby-3155-03-ag-backingStoreHashtableWithRowLocation.diff
          48 kB
          Rick Hillegas
        6. derby-3155-03-ah-backingStoreHashtableWithRowLocation.diff
          48 kB
          Rick Hillegas
        7. derby-3155-04-ae-deleteAction.diff
          135 kB
          Rick Hillegas
        8. derby-3155-04-af-deleteAction.diff
          140 kB
          Rick Hillegas
        9. derby-3155-05-aa-triggerTransitionTableAsTarget.diff
          2 kB
          Rick Hillegas
        10. derby-3155-06-aa-triggerTransitionTableAsSource.diff
          10 kB
          Rick Hillegas
        11. derby-3155-07-ad-insertAction.diff
          114 kB
          Rick Hillegas
        12. derby-3155-08-ah-updateAction.diff
          94 kB
          Rick Hillegas
        13. derby-3155-09-aa-correlationNames.diff
          20 kB
          Rick Hillegas
        14. derby-3155-10-aa-correlationNames.diff
          19 kB
          Rick Hillegas
        15. derby-3155-11-ab-beforeTriggersCantFireMerge.diff
          6 kB
          Rick Hillegas
        16. derby-3155-12-aa-canOmitInsertColumnList.diff
          5 kB
          Rick Hillegas
        17. derby-3155-13-aa-allowSystemAndTempTables.diff
          5 kB
          Rick Hillegas
        18. derby-3155-14-aa-replaceCorrelationNamesOnLeftSideOfSETclauses.diff
          4 kB
          Rick Hillegas
        19. derby-3155-15-aa-replumbMergeResultSetCleanup.diff
          4 kB
          Rick Hillegas
        20. derby-3155-16-aa-treatCurrentRowLocationNodeLikeBaseColumnNode.diff
          4 kB
          Rick Hillegas
        21. derby-3155-17-aa-serializingRowLocations.diff
          3 kB
          Rick Hillegas
        22. derby-3155-18-aa-basicView.diff
          3 kB
          Rick Hillegas
        23. derby-3155-19-aa-forbidSubqueriesInMatchedClauses.diff
          8 kB
          Rick Hillegas
        24. derby-3155-20-aa-reworkColumnMatching.diff
          30 kB
          Rick Hillegas
        25. derby-3155-21-ac-cleanupAndForbidSynonyms.diff
          17 kB
          Rick Hillegas
        26. derby-3155-22-ad-testIdentifiersOnLeftSideOfSetClauses.diff
          3 kB
          Rick Hillegas
        27. derby-3155-23-aa-forbidDerivedColumnLists.diff
          5 kB
          Rick Hillegas
        28. derby-3155-24-aa-supportParameters.diff
          3 kB
          Rick Hillegas
        29. derby-3155-25-aa-parametersAsInsertValues.diff
          6 kB
          Rick Hillegas
        30. derby-3155-26-aa-copyRowLocationForIndexScans.diff
          8 kB
          Rick Hillegas
        31. derby-3155-27-aa-adjustMatchingRefinements.diff
          8 kB
          Rick Hillegas
        32. derby-3155-28-aa-cardinalityViolations.diff
          13 kB
          Rick Hillegas
        33. derby-3155-29-aa-missingSchema.diff
          4 kB
          Rick Hillegas
        34. derby-3155-30-ab-moreCorrelationNames.diff
          34 kB
          Rick Hillegas
        35. derby-3155-31-aa-deletePrivs.diff
          34 kB
          Rick Hillegas
        36. derby-3155-32-aa-newTestFunction.diff
          0.7 kB
          Rick Hillegas
        37. derby-3155-33-ab-insertPrivs.diff
          21 kB
          Rick Hillegas
        38. derby-3155-34-aa-updatePrivs.diff
          20 kB
          Rick Hillegas
        39. derby-3155-34-ab-updatePrivs.diff
          22 kB
          Rick Hillegas
        40. derby-3155-35-aa-allPrivsTest.diff
          14 kB
          Rick Hillegas
        41. derby-3155-36-aa-lockModeComment.diff
          1.0 kB
          Rick Hillegas
        42. derby-3155-37-aa-printSubNodes.diff
          4 kB
          Rick Hillegas
        43. derby-3155-38-aa-datatypes.diff
          49 kB
          Rick Hillegas
        44. derby-3155-39-aa-fixBuild.diff
          2 kB
          Rick Hillegas
        45. derby-3155-40-aa-bigLobs.diff
          16 kB
          Rick Hillegas
        46. derby-3155-41-aa-nullGeneratedColumns.diff
          4 kB
          Rick Hillegas
        47. derby-3155-42-aa-triggersAndGeneratedColumns.diff
          8 kB
          Rick Hillegas
        48. derby-3155-43-aa-eliminateDuplicateColumnRefs.diff
          2 kB
          Rick Hillegas
        49. derby-3155-44-aa-lobsInTriggers.diff
          23 kB
          Rick Hillegas
        50. derby-3155-45-aa-serialization.diff
          10 kB
          Rick Hillegas
        51. derby-3155-46-aa-deferredDeletes.diff
          8 kB
          Rick Hillegas
        52. derby-3155-47-aa-collations.diff
          6 kB
          Rick Hillegas
        53. derby-3155-48-aa-indexScan.diff
          7 kB
          Rick Hillegas
        54. derby-3155-49-aa-cleanup1.diff
          34 kB
          Rick Hillegas
        55. derby-3155-50-aa-revampDeleteThenRows.diff
          15 kB
          Rick Hillegas
        56. derby-3155-51-aa-cleanup2.diff
          7 kB
          Rick Hillegas
        57. derby-3155-52-aa-upgrade.diff
          4 kB
          Rick Hillegas
        58. derby-3155-53-aa-transitionSimpleColumn.diff
          3 kB
          Rick Hillegas
        59. MergeStatement.html
          11 kB
          Rick Hillegas
        60. MergeStatement.html
          10 kB
          Rick Hillegas
        61. MergeStatement.html
          10 kB
          Rick Hillegas
        62. MergeStatement.html
          10 kB
          Rick Hillegas
        63. MergeStatement.html
          10 kB
          Rick Hillegas

          Issue Links

            Activity

              People

              • Assignee:
                rhillegas Rick Hillegas
                Reporter:
                trejkaz Trejkaz
              • Votes:
                8 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: