A way to INSERT ... ON DUPLICATE KEY UPDATE in JavaDB

Looking for information on how to do something like mySQL "INSERT ... ON DUPLICATE KEY UPDATE" where

"If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:"

in Java DB.

I have tried a few conditional statements with no success. Any help would be appreciated.

Thanks,
Elliott
LVL 1
maxinfoAsked:
Who is Participating?
 
CEHJConnect With a Mentor Commented:
Yes. That should normally be the case, depending on the transaction isolation level

http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html
0
 
glcumminsCommented:
You can use UPDATE INTO... This updates a record when it exists, and creates a new record when no match exists.
0
 
glcumminsCommented:
I apologize: I believe the correct syntax is "REPLACE INTO..."
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
maxinfoAuthor Commented:
14:58:17  [UPDATE - 0 row(s), 0.000 secs]  [Error Code: 30000, SQL State: 42X01]  Syntax error: Encountered "into" at line 2, column 8.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
0
 
maxinfoAuthor Commented:
14:58:52  [REPLACE - 0 row(s), 0.000 secs]  [Error Code: 30000, SQL State: 42X01]  Syntax error: Encountered "replace" at line 1, column 1.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I do not really see what your question is...
maybe give a bit more information?
0
 
maxinfoAuthor Commented:
I would like to run one SQL statement that will insert if the primary key doesn't exist and update it if it does.
0
 
modsiwCommented:
He wants to create a statement that:

if (an identical set of primary key information exists) preform an update statement with the primary keys as the where clause
else preform an insert statement
 
0
 
modsiwCommented:
JavaDB = derby
0
 
CEHJCommented:
Why not do a select first?
0
 
modsiwCommented:
Threaded application. Something could do an insert between the select and matching insert which will cause a crash.

This is how it is coded now.
0
 
maxinfoAuthor Commented:
I want an atomic SQL statement - I did try a select with a subquery for update or delete but either I did not do it correctly (if it can be done) or Java DB wouldn't allow it.
0
 
modsiwCommented:
"This is how it is coded now." The conditional logic is in the application, instead of the SQL statement.

We haven't been able to find a a way to put the condition in SQL
0
 
modsiwCommented:
The over all goals is:

To find a fail proof way to either INSERT or UPDATE depending on the existence of the primary key set on the DB for Derby.

We seem to have found one for MySQL:  http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html on this question http://www.experts-exchange.com/Database/MySQL/Q_22686635.html
0
 
CEHJCommented:
You just need one transaction containing both statements
0
 
modsiwCommented:
There's probably something I'm just not understanding here.

It seems to me that if I put an update and an insert into a single transaction. The insert would cause nothing to commit if the primary key combination exists.
0
 
maxinfoAuthor Commented:
I need something that performs this in a single transaction. Any conditional test I have tried in Java DB has failed.

update LOCALDB.TEST set COL1 = 0, COL2 = 0, COL3 = 0, COL4 = 0 where COL1 = 0
-- If  0 Rows Affected Then
insert into LOCALDB.TEST (COL1, COL2, COL3, COL4) values (0, 0, 0, 0)

-Elliott
0
 
CEHJCommented:
>>update and an insert into a single transaction.

You don't do an update and insert. You do a select/insert or select/update
0
 
modsiwCommented:
just for clarification.

If i preform a select on a table, it will lock that table from future writes until I call a commit?
0
 
modsiwCommented:
writes from a different connection, that is
0
 
objectsConnect With a Mentor Commented:
you do an update, if that fails (1 rows affected or exception depending on the driver) then you perform your insert. And perform both in a single transaction.
 
0
 
objectsCommented:
a stored procedure would be another approach.
You could then use a CallableStatement to invoke it

http://www.exampledepot.com/egs/java.sql/CallProcedure.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.