Where not exists causing problems for insert into, db2

Question

I have a query like so:

insert into mySchema.myTable (award_id, cust_id) values ('blahblah', 12345)
where not exists (select * from mySchema.myOtherTable where cust_id = 12345);

I am getting error:

ILLEGAL USE OF KEYWORD WHERE.  TOKEN FOR <END-OF-STATEMENT> NOT ATOMIC WAS EXPECTED SQL 
Code: -199, SQL State: 42601

I have seen a bunch of similar queries accepted as answers, and I don't understand why it's finding issue with this.

Could you provide a link to one of those answers. You're probably misreading it. — Barmar, Jun 05 '14 at 19:02
there is an answer about that in here http://stackoverflow.com/questions/330241/does-db2-have-an-insert-or-update-statement also here http://www.tek-tips.com/viewthread.cfm?qid=1560413 — Serge, Jun 05 '14 at 19:08
In your first link That insert statement mustaccio correctly points out that the query is syntactically incorrect. Look into MERGE if you haven't already, it's a very powerful statement. In the second link I can't see any similarities with the query you tried, am I missing something? — Lennart - Slava Ukraini, Jun 05 '14 at 19:41
I am familiar with merge statement, but I didn't want to use it since I would never be updating. — Serge, Jun 05 '14 at 19:45

score 7 · Accepted Answer · answered Jun 05 '14 at 19:31

7

This will work:

insert into mySchema.myTable (award_id, cust_id) 
select 'blahblah', 12345
from sysibm.sysdummy1
where not exists (select * from mySchema.myOtherTable where cust_id = 12345);

An alternative to sysibm.sysdummy1 would be:

insert into mySchema.myTable (award_id, cust_id) 
select 'blahblah', 12345
from ( values (1) )
where not exists (select * from mySchema.myOtherTable where cust_id = 12345);

answered Jun 05 '14 at 19:31

Lennart - Slava Ukraini

6,936
1
20
32

I had just answered the same thing without noticing your post, you get the check anyways though, thanks. – Serge Jun 05 '14 at 19:34
I recomend that you have a look at Merge. It solves this and other problems in a nice way – Lennart - Slava Ukraini Jun 28 '19 at 17:27

score 2 · Answer 2 · answered Jun 05 '14 at 19:24

2

I am not sure about that but you can do it like below though

INSERT INTO mySchema.myTable (award_id, cust_id) 
SELECT 'blahblah', 12345
WHERE NOT EXISTS
(
SELECT 1 FROM mySchema.myOtherTable WHERE cust_id = 12345
)

(OR)

IF NOT EXISTS (SELECT 1 FROM mySchema.myOtherTable WHERE cust_id = 12345)
BEGIN
    INSERT INTO mySchema.myTable (award_id, cust_id) 
    VALUES('blahblah', 12345)
END

answered Jun 05 '14 at 19:24

Rahul

76,197
13
71
125

So I got it to work with something similar to the first option. I'll post it in another answer below. – Serge Jun 05 '14 at 19:29

score 1 · Answer 3 · answered Jun 05 '14 at 19:32

1

insert into mySchema.myTable (award_id, cust_id) 
(select 'blahblah', 12345 from sysibm.sysdummy1
where not exists (select * from mySchema.myOtherTable where cust_id = 12345));

This ended up working.

answered Jun 05 '14 at 19:32

Serge

608
4
12
24

Where not exists causing problems for insert into, db2

3 Answers3