Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of lists@nabble.com designates
 216.139.236.26 as permitted sender)
Message-ID: <33016245.post@talk.nabble.com>
Date: Wed, 21 Dec 2011 03:59:04 -0800 (PST)
From: JulioSerje <jserje@gmail.com>
To: derby-user@db.apache.org
Subject: Re: UPDATE FROM JOIN
In-Reply-To: <4EEFA2FA.2090801@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <32989399.post@talk.nabble.com> <4EEF43E1.7010409@oracle.com>
 <33005383.post@talk.nabble.com> <4EEFA2FA.2090801@oracle.com>


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.

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

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