Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Unanswered: MERGE alternative on DB2 8?

    Hi,

    I need a statement (to be used on simple scripts, not procedures) which will check if a value exists before inserting.

    LIKE:

    INSERT INTO names("id", "name")
    VALUES
    (1, 'Dave'),
    (2, 'Thomas'),
    (3, 'Paul'),
    (4, 'Alex'),
    (5, 'Patrick'),
    (6, 'PJ')
    IF NOT EXISTS (SELECT * FROM names);

    How can I achieve that on DB2 8?

    Cheers

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    DB2 version 8 has a MERGE statement that will do that.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    See if this works (I don't have a DB2 v8 instance handy to test it):
    Code:
    INSERT INTO names("id", "name")
    select * from table (
    VALUES
    (1, 'Dave'),
    (2, 'Thomas'),
    (3, 'Paul'),
    (4, 'Alex'),
    (5, 'Patrick'),
    (6, 'PJ')
    ) t
    where NOT EXISTS (SELECT * FROM names);
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jun 2009
    Posts
    6
    Thanks guys!

    Unfortunately, it didn't!

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What error message(s) did you got?

    Why did you used double quotes?

    Anyway, try this
    Code:
    INSERT INTO names(id, name)
    SELECT *
      FROM (VALUES
            (1, 'Dave'),
            (2, 'Thomas'),
            (3, 'Paul'),
            (4, 'Alex'),
            (5, 'Patrick'),
            (6, 'PJ')
           ) t(id, name)
     WHERE (id, name) NOT IN
           (SELECT id, name FROM names)
    ;
    Last edited by tonkuma; 01-27-10 at 16:08. Reason: Changed "id NOT IN ..." to "(id, name) NOT IN ..."

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to use NOT EXISTS, try this
    Code:
    INSERT INTO names(id, name)
    SELECT *
      FROM (VALUES
            (1, 'Dave'),
            (2, 'Thomas'),
            (3, 'Paul'),
            (4, 'Alex'),
            (5, 'Patrick'),
            (6, 'PJ')
           ) t(id, name)
     WHERE NOT EXISTS
           (SELECT *
              FROM names n
             WHERE n.id   = t.id
               AND n.name = t.name)
    ;

  7. #7
    Join Date
    Jun 2009
    Posts
    6
    Thanks!

    It worked...

Posting Permissions

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