conditional statements in derby

Question

It looks like derby doesn't support conditional statements [IF]. How do we write

if exists (select 1 from customers where name='somename') 
    update customers ...
else 
    insert into customers ...

in derby? Derby doesn't have 'replace into' of mysql either.

Note: I am using derby as unit-testing replacement for mysql [that is production will use mysql and unit-test will use derby].

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

What about http://db.apache.org/derby/docs/10.2/ref/rrefcasenullif.html#rrefcasenullif?

   CASE
      WHEN 1 = 2 THEN 3
      WHEN 4 = 5 THEN 6
      ELSE 7
   END

So maybe you can try something like:

CASE
 WHEN  select 1 from customers where name='somename' = 1 THEN update...
ELSE  insert...
END

I have no idea if that would work but it seems like a start. Good luck!

edit: After trying a few of these things out...I don't know if this will really help you. It doesn't seem like you can fire switch between SELECT and INSERT; it has to be one or the other and the CASE goes inside. What you want to do may or may not be possible...

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 23 '11 at 18:45

lampShaded

9,215
2
18
12

This will not work. Poorly documented Derby DB does not mention that CASE clause can only be used as a part of another SQL clause e.g. SELECT. For instance, SELECT CASE COUNT(*) WHEN 1 THEN 10 ELSE 20 END FROM WHERE
– Andreas Aug 28 '17 at 16:09

score 1 · Answer 2 · answered Feb 10 '19 at 00:40

I know this might be too late but in case anyone is still looking for it:

MERGE

https://issues.apache.org/jira/browse/DERBY-3155

For Example:

MERGE INTO customers USING SYSIBM.SYSDUMMY1 ON customers.name='somename' WHEN MATCHED THEN UPDATE SET name = 'someothername', ... WHEN NOT MATCHED THEN INSERT(id, name, ...) VALUES (DEFAULT, 'someothername', ...)

conditional statements in derby

2 Answers2

Linked