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"?

If you're not tied to Derby, give H2 a spin - it's [faster](http://www.h2database.com/html/performance.html), and has a very nice and simple [MERGE](http://www.h2database.com/html/grammar.html#merge) verb. — Tom Anderson, Feb 22 '11 at 13:15

score 16 · Answer 1 · answered Jan 10 '09 at 12:56

16

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.

answered Jan 10 '09 at 12:56

Snukker

1,963
3
18
18

That's a nice trick. Can it be made to work with multiple rows? Probably, using the VALUES syntax, but i'm not certain. – Tom Anderson Feb 22 '11 at 13:11
Works also for HSQLDB! Very nice! – binwiederhier Mar 14 '14 at 21:58
Using `MERGE` is actually way better; check out this answer: http://stackoverflow.com/questions/2609996/atomic-insert-select-in-hsqldb – binwiederhier Mar 14 '14 at 22:58
This causes `Error code 0, SQL state XJ001: Java exception: '8: java.lang.ArrayIndexOutOfBoundsException'.` when used as batch – Hooli Jul 29 '16 at 18:18

score 4 · Answer 2 · answered Oct 21 '09 at 04:12

4

The request to support the SQL:2003 MERGE statement is logged in the Derby bug tracker as https://issues.apache.org/jira/browse/DERBY-3155

You could vote for that issue, or, even better, contribute an implementation!

Otherwise, the only solutions I know of require either selecting the row first to see if it exists, or inserting it and catching the exception, as others have noted.

You can package this logic up in a database procedure to make it somewhat easier to execute.

answered Oct 21 '09 at 04:12

Bryan Pendleton

16,128
3
32
56

1

Urgh. The SQL:2003 MERGE is horrific. Standard, and flexible, but so much text just to upsert a row! – Tom Anderson Feb 22 '11 at 13:12
I agree... so much duplication (same goes for the classical trick above.) I wonder if there is a standard alternative, because if I had found one at the time, I would have feature requested that instead. – Hakanai Jul 29 '11 at 04:54

score 3 · Answer 3 · answered Apr 01 '09 at 14:13

A standard way I've used with PostgreSQL DBs is something like the following:

INSERT INTO foo ( col1, col2, col3, ... )
SELECT 'col1 value', 'col2 value', 'colc value', ...
WHERE NOT EXISTS (
  SELECT 0
  FROM foo
  WHERE col1 = 'col1 value'
  ...
)

Not sure how portable or strictly ANSI compliant it is though. The missing FROM clause in the outer SELECT statement particularly may be non-standard. Give it a go though.

score 2 · Answer 4 · answered Aug 31 '14 at 07:18

2

Derby implemented MERGE in 10.11: https://db.apache.org/derby/docs/10.11/ref/rrefsqljmerge.html
Note that you need to upgrade your DB to 10.11 before using it: https://db.apache.org/derby/docs/10.11/devguide/cdevupgrades.html

answered Aug 31 '14 at 07:18

Alex

82
9

Kjartan · Answer 5 · 2015-06-26T10:13:25.000

1

I had the same problem, and I got this working for an insert with just one value / column on Derby. (I never got around to testing it with more, but I have no reason to assume it should not though):

INSERT INTO my_table (my_column)
    (SELECT 'new_value_to_insert'
    FROM my_table
    WHERE my_column = 'new_value_to_insert' 
    HAVING count(*)=0)

edited Jun 26 '15 at 10:13

answered Mar 16 '10 at 22:05

Kjartan

18,591
15
71
96

1

Perfect! I know these are different databases than the question, but this solution works for me with PostgreSQL and HSQLDB. – Jeff Fairley Sep 25 '14 at 20:29

score 0 · Answer 6 · answered Jul 27 '09 at 22:10

There is no native support for this, to get round it I'm using eclipse-link, eclipse-link will try to create the tables and ignore any errors that arise from attempting to create tables that already exists.

If you make schema changes, you can tell eclipse link to drop the tables before creating them.

score 0 · Answer 7 · answered Sep 29 '09 at 08:52

I'am using this solution but you must use it only if you understanding the difference between the duplicate from the database view and the duplicate from the user view

Duplicates from the database view are two records with the same primary key

Duplicates from the user view are two records with all the fiels identical

    while (ResultSet.next()) {
    try {
    PreparedStatement insertion = myConn.prepareStatement("insert into table values (?)");
    insertion .setString(1, "test");
    insertion .executeUpdate();           
    } catch (SQLException e) {
        if(e.getSQLState().equals("23505"))//Found duplicate from database view
         {continue;}//ignore duplicate and continue with the insert statement
        else{try {                           
              throw e;
        } catch (Exception ex) {  
            }
          }
        }
      }

Would't the second try-catch ensure that nothing ever gets thrown.? Essentially the whole catch(SQLException) block will behave exactly the same as it would if it were completely empty — Newtopian, Sep 07 '16 at 17:41

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

7 Answers7