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 Wed, 21 Dec 2011 13:31:08 GMT
On 12/21/11 3:59 AM, JulioSerje wrote:
> Hi Rick,
>
> Unfortunately our expressions would require a high number of subqueries;
> ours is an engineering application that require many values from several
> tables in order to compute multiple complex formulae.
>
> In our specific case we know that by design that our UPDATE ..FROM
> statements will compute the same as the statement based on subqueries (i.e.
> statements are not ambiguous at all).
>
> The question is, are you aware of any plans to implement MERGE in Derby? In
> the meanwhile we are considering the  use of an intermediate table with
> results INSERTed, as insert can use a full fledged select clause, and later
> use a much simpler UPDATE statement with less and simpler subqueries.
Hi Julio,

There is an existing enhancement request for the MERGE operator: 
https://issues.apache.org/jira/browse/DERBY-3155 If you add your vote on 
that issue, you will increase its chances of being implemented.

Hope this helps,
-Rick
> Thanks again,
>
> Julio
>
>
>
> Are there any plans to
>
> Rick Hillegas-3 wrote:
>> Hi Julio,
>>
>> I haven't looked into the details of what the Derby optimizer does in
>> the case of multiple SET clauses, each driven by its own subselect. It
>> is likely that this is an underoptimized case. If you rewrite your query
>> to use subselects in the SET clauses, and you experience poor
>> performance on Derby, you may be able to get advice on this list about
>> how to improve that performance.
>>
>> It is possible that the Derby UDPATE will compute different results than
>> the UPDATE...FROM statements in your other databases. Derby may even
>> raise an error. If Derby raises an error, this may be a warning to you
>> that your UPDATE...FROM statements are ambiguous. An UPDATE...FROM
>> statement may end up updating the same column multiple times in a
>> non-deterministic way. This can happen if the join in the FROM clause
>> returns multiple joined rows for each row in the table you are updating.
>> In this situation, the standard syntax will raise an error because the
>> subselects are supposed to return one row each. More information on this
>> problem can be found here:
>> http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
>>
>> Hope this is helpful,
>> -Rick
>>
>>


Mime
View raw message