Modifying Db2 data by using MERGE statements

Example MERGE statements

• For activities whose description has been changed, update the description in the RECORDS table. For new activities, insert into the RECORDS table. The RECORDS and ACTIVITIES tables both have ACTIVITY as a primary key.

  MERGE INTO RECORDS AR
   USING (SELECT ACTIVITY, DESCRIPTION FROM ACTIVITIES) AC
   ON (AR.ACTIVITY = AC.ACTIVITY)
   WHEN MATCHED THEN
    UPDATE SET
    DESCRIPTION = AC.DESCRIPTION
   WHEN NOT MATCHED THEN
    INSERT
    (ACTIVITY, DESCRIPTION)
    VALUES (AC.ACTIVITY, AC.DESCRIPTION);

• Using the SHIPMENT table, merge rows into the INVENTORY table: Increase the quantity by the part count in the SHIPMENT table for rows that match; else insert a row for the new part number into the inventory table.

  MERGE INTO INVENTORY AS IN
   USING (SELECT PARTNO, DESCRIPTION, COUNT FROM SHIPMENT
   WHERE SHIPMENT.PARTNO IS NOT NULL) AS SH
   ON (IN.PARTNO = SH.PARTNO)
   WHEN MATCHED THEN
    UPDATE SET
     DESCRIPTION = SH.DESCRIPTION,
     QUANTITY = IN.QUANTITY + SH.COUNT
   WHEN NOT MATCHED THEN
    INSERT
    (PARTNO, DESCRIPTION, QUANTITY)
    VALUES (SH.PARTNO, SH.DESCRIPTION, SH.COUNT);

• Using the TRANSACTION table, merge rows into the ACCOUNT table: Update the balance from the set of transactions against an account ID, and insert new accounts from the consolidated transactions where they do not already exist.

  MERGE INTO ACCOUNT AS A
   USING (SELECT ID, SUM(AMOUNT) SUM_AMOUNT FROM TRANSACTION
    GROUP BY ID) AS T
    ON A.ID = T.ID
   WHEN MATCHED THEN
    UPDATE SET
     BALANCE = A.BALANCE + T.SUM_AMOUNT
   WHEN NOT MATCHED THEN
    INSERT
    (ID, BALANCE)
     VALUES (T.ID, T.SUM_AMOUNT);

• Using the TRANSACTION_LOG table, merge rows into the EMPLOYEE_FILE table: Update the phone and office with the latest TRANSACTION_LOG row based on the transaction time, and insert the latest new EMPLOYEE_FILE row where the row does not already exist.

  MERGE INTO EMPLOYEE_FILE AS E
   USING (SELECT EMPID, PHONE, OFFICE
    FROM (SELECT EMPID, PHONE, OFFICE,
    ROW_NUMBER() OVER (PARTITION BY EMPID
    ORDER BY TRANSACTION_TIME DESC) RN
    FROM TRANSACTION_LOG) AS NT
    WHERE RN = 1) AS T
    ON E.EMPID = T.EMPID
   WHEN MATCHED THEN
    UPDATE SET
     (PHONE, OFFICE) =
     (T.PHONE, T.OFFICE)
   WHEN NOT MATCHED THEN
    INSERT
    (EMPID, PHONE, OFFICE)
    VALUES (T.EMPID, T.PHONE, T.OFFICE);

• Update the list of activities organized by Group A in the RECORDS table. Delete all outdated activities and update the activities information (DESCRIPTION and DATE) in the RECORDS table if they have been changed. For new upcoming activities, insert into the RECORDS table. Signal an error if the date of the activity is not known. The date of the activities in the RECORDS table must be specified. Each group has an activities table. For example, ACTIVITIES_GROUPA contains all activities that group A organizes, and the RECORDS table contains all upcoming activities organized by different groups in a company. The RECORDS table has (GROUP, ACTIVITY) as the primary key, and DATE is not nullable. All activities tables have ACTIVITY as the primary key. The LAST_MODIFIED column in the RECORDS table is defined with CURRENT TIMESTAMP as the default value.

  MERGE INTO RECORDS AR
   USING (SELECT ACTIVITY, DESCRIPTION, DATE, LAST_MODIFIED
    FROM ACTIVITIES_GROUPA) AC
    ON (AR.ACTIVITY = AC.ACTIVITY) AND AR.GROUP = 'A'
   WHEN MATCHED AND AC.DATE IS NULL THEN
    SIGNAL SQLSTATE '70001'
     SET MESSAGE_TEXT =
      AC.ACTIVITY CONCAT ' CANNOT BE MODIFIED. REASON: DATE IS NOT KNOWN'
   WHEN MATCHED AND AC.DATE < CURRENT DATE THEN
    DELETE
   WHEN MATCHED AND AR.LAST_MODIFIED < AC.LAST_MODIFIED THEN
    UPDATE SET
    (DESCRIPTION, DATE, LAST_MODIFIED) = (AC.DESCRIPTION, AC.DATE, DEFAULT)
   WHEN NOT MATCHED AND AC.DATE IS NULL THEN
    SIGNAL SQLSTATE '70002'
     SET MESSAGE_TEXT =
      AC.ACTIVITY CONCAT ' CANNOT BE INSERTED. REASON: DATE IS NOT KNOWN'
   WHEN NOT MATCHED AND AC.DATE >= CURRENT DATE THEN
    INSERT
     (GROUP, ACTIVITY, DESCRIPTION, DATE)
     VALUES ('A', AC.ACTIVITY, AC.DESCRIPTION, AC.DATE)
   ELSE IGNORE;

• Update the descriptions for activities that exist in the RECORDS table. Otherwise, insert the activity and its description into the RECORDS table.

  MERGE INTO RECORDS AR
    USING (VALUES (:hv_activity, :hv_description)
      FOR :hv_nrows ROWS)
      AS AC (ACTIVITY, DESCRIPTION)
    ON (AR.ACTIVITY = AC.ACTIVITY)
    WHEN MATCHED THEN UPDATE SET DESCRIPTION = AC.DESCRIPTION
    WHEN NOT MATCHED THEN INSERT (ACTIVITY, DESCRIPTION)
       VALUES (AC.ACTIVITY, AC.DESCRIPTION)
    NOT ATOMIC CONTINUE ON SQLEXCEPTION;

• Use the transaction data to merge rows into the account table. Update the balance from the transaction data against an account ID and insert new accounts from the transaction data where the accounts do not already exist.

  MERGE INTO ACCOUNT AS A
    USING (VALUES (:hv_id, :hv_amount) 
      FOR 3 ROWS)
      AS T (ID, AMOUNT)
    ON (A.ID = T.ID)
    WHEN MATCHED THEN UPDATE SET BALANCE = A.BALANCE + T.AMOUNT
    WHEN NOT MATCHED THEN INSERT (ID, BALANCE) 
        VALUES (T.ID, T.AMOUNT)
    NOT ATOMIC CONTINUE ON SQLEXCEPTION;

• Update the list of activities that are organized by group A in the RECORDS table. Update the activities information (description and date when last modified) in the RECORDS table if the activities exist in the RECORDS table and are also organized by group A. Insert new activities into the RECORDS table.

  -- hv_nrows = 3
  -- hv_activity(1) = 'D'; hv_description(1) = 'Dance'; hv_date(1) = '03/01/07'
  -- hv_activity(2) = 'S'; hv_description(2) = 'Singing'; hv_date(2) = '03/17/07'
  -- hv_activity(3) = 'T'; hv_description(3) = 'Tai-chi'; hv_date(3) = '05/01/07'
  -- hv_group = 'A';
  -- note that hv_group is not an array. All 3 values contain the same values
  MERGE INTO RECORDS AR
    USING (VALUES (:hv_activity, :hv_description, :hv_date, :hv_group)
       FOR :hv_nrows ROWS)
       AS AC (ACTIVITY, DESCRIPTION, DATE, GROUP)
    ON AR.ACTIVITY = AC.ACTIVITY AND AR.GROUP = AC.GROUP
    WHEN MATCHED 
    THEN UPDATE SET (DESCRIPTION, DATE, LAST_MODIFIED)
                    = (AC.DESCRIPTION, AC.DATE, CURRENT TIMESTAMP)
    WHEN NOT MATCHED
    THEN INSERT (GROUP, ACTIVITY, DESCRIPTION, DATE, LAST_MODIFIED)
       VALUES (AC.GROUP, AC.ACTIVITY, AC.DESCRIPTION, AC.DATE, CURRENT TIMESTAMP)
    NOT ATOMIC CONTINUE ON SQLEXCEPTION;

• Use two arrays, CHARA and INTA, as input to a MERGE statement. Column COL2 is set to the cardinality of CHARA for matching rows, and COL2 is set to the cardinality of INTA for non-matching rows.

  CREATE TYPE INTARRAY AS INTEGER ARRAY[6];
  CREATE TYPE CHARARRAY AS CHAR(20) ARRAY[7];
  CREATE VARIABLE INTA AS INTARRAY;
  CREATE VARIABLE CHARA AS CHARARRAY;
  CREATE VARIABLE SI INT;
  SET CHARA = ARRAY['a', 'b', 'c'];
  SET INTA = ARRAY [1, 2, 3, 4, 5];
  CREATE TABLE T1 (COL1 CHAR(7), COL2 INT);
  INSERT INTO T1 VALUES ('abc', 10);
  MERGE INTO T1 AS A
   USING TABLE (VALUES ('rsk', 3 ) ) AS T (ID, AMOUNT)
   ON A.COL1 = T.ID
   WHEN MATCHED
    THEN UPDATE SET COL2 = CARDINALITY(CHARA)
   WHEN NOT MATCHED
    THEN INSERT (COL1, COL2 ) VALUES (T.ID, CARDINALITY(INTA));