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].

Answer 1

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...

Answer 2

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', ...)