db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag Wanvik <dag.wan...@oracle.com>
Subject Re: UPDATE OR INSERT Query in Derby
Date Sat, 05 Jan 2013 02:02:48 GMT

On 04.01.2013 02:42, Rick Hillegas wrote:
> Hi Sekhar,
>
> Derby does not support the Firebird-specific syntax. The SQL Standard
> solution is the MERGE statement. Derby does not support the MERGE
> statement yet, but there is an open enhancement request for this
> feature: https://issues.apache.org/jira/browse/DERBY-3155. You are
> welcome to raise the priority of DERBY-3155 by voting for it.
>
> One workaround would be to write a database procedure which probes the
> table to see if the row exists, and then issues an INSERT or an UPDATE
> depending on the result of that probe.

You can also use an updatable result set on a suitable query, then
navigate (#next) to the existing row and update it, or create a new row
if the navigate fails (no existing matching row).

Cf. ResultSet#updateRow
(http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#updateRow%28%29)
and ResultSet#moveToInsertRow / ResultSet#insertRow.

Thanks,
Dag



>
> Hope this helps,
> -Rick
>
> On 1/3/13 4:48 AM, kosurusekhar wrote:
>> Hi
>>
>> I need to execute following kind of query.
>>
>> *UPDATE OR INSERT tableName (column1, column2, column3) values
>> (col1value,
>> col2value, col3value) matching (column1);*
>>
>> The above query will execute in Firebird database, now we are
>> migrating from
>> firebird to Derby database.
>>
>> here *column1* is not primary key column. if the *col1value* is already
>> available in table  *column1* then we have to update remaining column
>> values, if the value is not present in column1 then we have to create
>> a new
>> record.
>>
>> Please help me to solve above scenario with one or optimized query.
>>
>> Thanks In Advance.
>>
>> Regards
>> Sekhar.
>>
>>
>>
>>
>> -- 
>> View this message in context:
>> http://apache-database.10148.n7.nabble.com/UPDATE-OR-INSERT-Query-in-Derby-tp126210.html
>> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>>
>


Mime
View raw message