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