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.

Answer 2

Hello,

I've tried your solution with HAVING and got "Network protocol exception: actual code point, 4,692, does not match expected code point, 9,224. The connection has been terminated."

I guess it means some internal problems in Derby. "Limbic System", did you try this statement on your DB?

Dime.

Answer 3

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.

Answer 4

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.

Answer 5

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) {  
              }
            }
          }
        }
  

Answer 6

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.

Answer 7

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)