Derby
  1. Derby
  2. DERBY-3155

Support for SQL:2003 MERGE statement

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major 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.

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

        Issue Links

          Activity

          No work has yet been logged on this issue.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development