db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: UPDATE FROM JOIN
Date Mon, 19 Dec 2011 14:02:09 GMT
On 12/16/11 1:42 PM, JulioSerje wrote:
> Is there any way to implement an update using a JOIN?  The reference manual
> only allows for one table to be updated:
>
> UPDATE table-Name [[AS] correlation-Name]
>    SET column-Name = Value
>    [ , column-Name = Value} ]*
>    [WHERE clause]
>
> We have an application where need to run many queries like:
>
> update t1  set t1.a=t2.b, t1.c=t2.d,...t1.x=(t2.a+t2.b/t2.y)
>         from Table1 t1 join Table2 t2 on t1.k=t2.k
>         where t1.x=1  and t2.y=2
>
> We offer support on our app to most data platforms (Oracle, MySql,
> SQLServer, PostgreSQL, SQLite, even Access..) and in all of them there is a
> way of doing this...
>
> Is this something missing in Derby?
>
> Any ideas highly appreciated.
>
Hi Julio,

UPDATE...FROM is a useful statement which appears in many SQL dialects. 
However, it does not appear in the standard ANSI/ISO SQL dialect which 
Derby implements, not even in the recently published 2011 version of the 
standard dialect.

A standard approach to updating a column from a join is to use 
subqueries in the SET clause. E.g., something like this:

     update t1 set a = ( select t2.a from t1, t2 where t1.b = t2.b );

Hope this helps,
-Rick

Mime
View raw message