Results 1 to 6 of 6

Thread: Merge

  1. #1
    Join Date
    Dec 2003
    Posts
    76

    Unanswered: Merge

    Hi,

    I have an question about using MERGE.

    I have a table DUMMY_TABLE:
    Code:
    --CREATION OF A DUMMY TABLE:
    create table DUMMY_TABLE
    (
      cod_dummy  number not null,
      desc_dummy varchar2(150) not null,
      data_dummy date not null,
      deleted    number(1) default 0
    )
    tablespace <namespace>
      storage
      (
        initial 64K
        minextents 1
        maxextents unlimited
      );
    
    alter table DUMMY_TABLE
      add constraint PK_DUMMY_TABLE primary key (COD_DUMMY);
    In that DUMMY_TABLE I have the following data:
    Code:
    -- INSERTION OF SOME TEST RECORDS
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (1, 'Number 1', sysdate-100, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (2, 'Number 2', sysdate-90, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (3, 'Number 3', sysdate-80, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (4, 'Number 4', sysdate-70, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (5, 'Number 5', sysdate-60, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (6, 'Number 6', sysdate-50, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (7, 'Number 7', sysdate-40, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (8, 'Number 8', sysdate-30, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (9, 'Number 9', sysdate-20, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (10, 'Number 10', sysdate-10, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (12, 'Number 12 - Deprecated', sysdate-55, 1);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (13, 'Number 13 - Deprecated', sysdate-35, 1);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (14, 'Number 14 - Deprecated', sysdate-25, 1);
    I was trying to use the MERGE onto that table, like this:
    Code:
    -- THIS WAS SUPPOSED TO INSERT THE RECORD BUT IT NOT WORKS
    MERGE INTO dummy_table d 
    USING (
          SELECT cod_dummy cod FROM dummy_table WHERE cod_dummy = 15) h
    ON (d.cod_dummy = h.cod) 
    
    WHEN NOT MATCHED THEN 
    INSERT (d.cod_dummy, d.desc_dummy) 
    VALUES (15, 'Dummy 15 New')
    
    WHEN MATCHED THEN 
    UPDATE SET d.desc_dummy = 'test merge update';
    After a few tests I've noticed that this script doesn't work because there is no DUMMY_CODE = 15.
    If I change the DUMMY_CODE to 14 in that script than it will work fine and make the Update.

    Code:
    -- THIS WORKS AND MAKES THE UPDATE AS SUPPOSED
    MERGE INTO dummy_table d 
    USING (
          SELECT cod_dummy cod FROM dummy_table WHERE cod_dummy = 14) h
    ON (d.cod_dummy = h.cod) 
    
    WHEN NOT MATCHED THEN 
    INSERT (d.cod_dummy, d.desc_dummy) 
    VALUES (14, 'Dummy 14 New')
    
    WHEN MATCHED THEN 
    UPDATE SET d.desc_dummy = 'test merge update';

    Is there any way to use MERGE this way or shall I begin to find an alternativa, like an regular INSERT (when does NOT EXISTS ...) or regular UPDATE?

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this, perhaps?
    Code:
    SQL> MERGE INTO dummy_table d
      2  USING (SELECT 15 cod        FROM dual
      3         MINUS
      4         SELECT cod_dummy cod FROM dummy_table) h
      5  ON (d.cod_dummy = h.cod)
      6  WHEN NOT matched THEN
      7    INSERT (d.cod_dummy,
      8            d.desc_dummy,
      9            data_dummy)
     10    VALUES (15,
     11            'Dummy 15 New',
     12            SYSDATE)
     13  WHEN matched THEN
     14    UPDATE SET d.desc_dummy = 'test merge update';
    
    1 row merged.
    
    SQL> select * From dummy_table;
    
     COD_DUMMY DESC_DUMMY                     DATA_DUMMY    DELETED
    ---------- ------------------------------ ---------- ----------
             1 Number 1                       09.11.2013          0
             2 Number 2                       19.11.2013          0
             3 Number 3                       29.11.2013          0
             4 Number 4                       09.12.2013          0
             5 Number 5                       19.12.2013          0
             6 Number 6                       29.12.2013          0
             7 Number 7                       08.01.2014          0
             8 Number 8                       18.01.2014          0
             9 Number 9                       28.01.2014          0
            10 Number 10                      07.02.2014          0
            12 Number 12 - Deprecated         24.12.2013          1
            13 Number 13 - Deprecated         13.01.2014          1
            14 Number 14 - Deprecated         23.01.2014          1
            15 Dummy 15 New                   17.02.2014          0
    
    14 rows selected.
    
    SQL>

  3. #3
    Join Date
    Dec 2003
    Posts
    76
    Hi,

    Thanks for your help but, altough it works fine when the value does not exist, it does not work when the cod_dummy already exists.
    When the value does not exist at the cod_dummy column, it works fine and inserts the new record but when the value exists (14 for instance) it does not make the update.
    I've tried with the INTERSECT operator also, but with same results.

    Altough I've managed to have some interesting results with the following code:
    Code:
    MERGE INTO dummy_table d
    USING (SELECT '#' || (select cod_dummy cod FROM dummy_table WHERE cod_dummy = 16) cod FROM DUAL) h
     ON ('#' || d.cod_dummy = h.cod)
    WHEN NOT MATCHED THEN
     INSERT (d.cod_dummy,
            d.desc_dummy,
               data_dummy)
       VALUES (16,
              'Dummy 16 New',
                SYSDATE)
    
     WHEN MATCHED THEN
       UPDATE SET d.desc_dummy = 'Merging ... merge and destroy' ;
    I would like to ask if this might be an solution to this issue if there is any kind of problem with it?

    Thanks
    Last edited by aucrun; 02-18-14 at 05:41.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Example 1:
    Code:
    MERGE INTO
          dummy_table d
    USING (SELECT 15 cod FROM dual
          ) h
     ON   d.cod_dummy = h.cod
    WHEN NOT matched THEN
    INSERT
    ( cod_dummy
    , desc_dummy
    , data_dummy
    )
    VALUES
    ( h.cod
    , 'Dummy ' || h.cod || ' New'
    ,  sysdate
    )
    WHEN matched THEN
    UPDATE
     SET   desc_dummy = 'test merge update'
    ;
    Last edited by tonkuma; 02-18-14 at 09:22. Reason: Add a line "Example 1:".

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a test result on DB2.

    Test Data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT cod_dummy
         , VARCHAR(desc_dummy , 30) AS desc_dummy_first30char
         , data_dummy
         , deleted
     FROM  dummy_table
    ;
    ------------------------------------------------------------------------------
    
    COD_DUMMY   DESC_DUMMY_FIRST30CHAR         DATA_DUMMY DELETED
    ----------- ------------------------------ ---------- -------
              1 Number 1                       2013-11-10       0
              2 Number 2                       2013-11-20       0
              3 Number 3                       2013-11-30       0
              4 Number 4                       2013-12-10       0
              5 Number 5                       2013-12-20       0
              6 Number 6                       2013-12-30       0
              7 Number 7                       2014-01-09       0
              8 Number 8                       2014-01-19       0
              9 Number 9                       2014-01-29       0
             10 Number 10                      2014-02-08       0
             12 Number 12 - Deprecated         2013-12-25       1
             13 Number 13 - Deprecated         2014-01-14       1
             14 Number 14 - Deprecated         2014-01-24       1
    
      13 record(s) selected.
    Merge statements:
    Code:
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO
          dummy_table d
    USING (SELECT 15 cod FROM /*dual*/ sysibm.sysdummy1
          ) h
     ON   d.cod_dummy = h.cod
    WHEN NOT matched THEN
    INSERT
    ( cod_dummy
    , desc_dummy
    , data_dummy
    )
    VALUES
    ( h.cod
    , 'Dummy ' || h.cod || ' New'
    ,  sysdate
    )
    WHEN matched THEN
    UPDATE
       SET desc_dummy = 'test merge update'
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO
          dummy_table d
    USING (SELECT 14 cod FROM /*dual*/ sysibm.sysdummy1
          ) h
     ON   d.cod_dummy = h.cod
    WHEN NOT matched THEN
    INSERT
    ( cod_dummy
    , desc_dummy
    , data_dummy
    )
    VALUES
    ( h.cod
    , 'Dummy ' || h.cod || ' New'
    ,  sysdate
    )
    WHEN matched THEN
    UPDATE
       SET desc_dummy = 'test merge update'
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    The result:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT cod_dummy
         , VARCHAR(desc_dummy , 30) AS desc_dummy_first30char
         , data_dummy
         , deleted
     FROM  dummy_table
    ;
    ------------------------------------------------------------------------------
    
    COD_DUMMY   DESC_DUMMY_FIRST30CHAR         DATA_DUMMY DELETED
    ----------- ------------------------------ ---------- -------
              1 Number 1                       2013-11-10       0
              2 Number 2                       2013-11-20       0
              3 Number 3                       2013-11-30       0
              4 Number 4                       2013-12-10       0
              5 Number 5                       2013-12-20       0
              6 Number 6                       2013-12-30       0
              7 Number 7                       2014-01-09       0
              8 Number 8                       2014-01-19       0
              9 Number 9                       2014-01-29       0
             10 Number 10                      2014-02-08       0
             12 Number 12 - Deprecated         2013-12-25       1
             13 Number 13 - Deprecated         2014-01-14       1
             14 test merge update              2014-01-24       1
             15 Dummy 15 New                   2014-02-18       0
    
      14 record(s) selected.
    Last edited by tonkuma; 02-18-14 at 09:37.

  6. #6
    Join Date
    Dec 2003
    Posts
    76
    I could swear that yesterday I've tried out the DUAL alternative and didn't manage to get results.
    Well doesn't matter ... anymore.

    This Is It.

    Thanks alot Tonkuma and everyone else.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •