MERGE alternative on DB2 8?

jfbaro · #1 (**permalink**) 01-27-10, 12:49

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

ARWinner · #2 (**permalink**) 01-27-10, 12:54

DB2 version 8 has a MERGE statement that will do that.

Andy

n_i · #3 (**permalink**) 01-27-10, 13:30

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);

jfbaro · #4 (**permalink**) 01-27-10, 14:45

Thanks guys!

Unfortunately, it didn't!

tonkuma · #5 (**permalink**) 01-27-10, 15:04

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)
;

tonkuma · #6 (**permalink**) 01-27-10, 15:17

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)
;

jfbaro · #7 (**permalink**) 01-28-10, 08:54

Thanks!

It worked...

Internet Services

Web Development

Digital Marketing

Consumer Tech