[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1650
  • Last Modified:

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
0
maxinfo
Asked:
maxinfo
  • 8
  • 5
  • 4
  • +3
2 Solutions
 
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
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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
 
CEHJCommented:
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
 
objectsCommented:
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 8
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now