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

Support for SQL:2003 MERGE statement

    XMLWordPrintableJSON

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

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: