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. >> >
