Apache Derby: how can I do "insert if not exists"?

Question

I'm giving Apache Derby, aka JavaDB a spin. I can't seem to get around duplicate key issues when inserting records that may already exist. Is there a Derby equivalent to "insert if not exists", or "merge" ?

Similarly, is there a way to do something like "drop table foo if exists"?

Answer 1

I've never used apache derby, but a general solution that is fairly database independent is as follows:

To insert the values 'a' and 'b' into table foo (with columns named A, B), but only where the values are not already there, try something like

INSERT INTO foo (  
  SELECT 'a' as A, 'b' as B
  FROM foo  
  WHERE  
    A = 'a' AND B = 'b'  
  HAVING count(*)=0  
 )

This may need tweaking for a particular dbms, but the idea is to insert the result of a select that only returns values when there are non.

This is a useful trick for creating an idempotent sql script (one that does nothing the second time it is run). However, be careful when using this in production code as the HAVING count(*)=0 may be very slow on large tables.