db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From JulioSerje <jse...@gmail.com>
Subject Re: UPDATE FROM JOIN
Date Mon, 19 Dec 2011 20:24:13 GMT



Rick Hillegas-3 wrote:
> 
> 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
> 
> 

Thanks a lot for your response, Rick.

This, despite being a standard is a much cumbersome way of doing what in
other dialects is straightforward. I believe this may be seen as a weakness
of Derby engine (- and maybe of the standard itself).

 A second question would be if performance of a 'standard' update with quite
a few sub queries will be the same or comparable to an UPDATE ... FROM ...
type of query. Does Derby internal optimizer recognize this situation?

-- 
View this message in context: http://old.nabble.com/UPDATE-FROM-JOIN-tp32989399p33005383.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message