From derby-user-return-14098-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed Dec 21 13:31:43 2011 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 765C2791A for ; Wed, 21 Dec 2011 13:31:43 +0000 (UTC) Received: (qmail 72460 invoked by uid 500); 21 Dec 2011 13:31:43 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 72435 invoked by uid 500); 21 Dec 2011 13:31:43 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 72428 invoked by uid 99); 21 Dec 2011 13:31:43 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Dec 2011 13:31:43 +0000 X-ASF-Spam-Status: No, hits=0.0 required=5.0 tests=SPF_PASS,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [148.87.113.117] (HELO rcsinet15.oracle.com) (148.87.113.117) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Dec 2011 13:31:33 +0000 Received: from acsinet22.oracle.com (acsinet22.oracle.com [141.146.126.238]) by rcsinet15.oracle.com (Switch-3.4.4/Switch-3.4.4) with ESMTP id pBLDVBSL024960 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Wed, 21 Dec 2011 13:31:12 GMT Received: from acsmt356.oracle.com (acsmt356.oracle.com [141.146.40.156]) by acsinet22.oracle.com (8.14.4+Sun/8.14.4) with ESMTP id pBLDVA6M003949 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Wed, 21 Dec 2011 13:31:11 GMT Received: from abhmt116.oracle.com (abhmt116.oracle.com [141.146.116.68]) by acsmt356.oracle.com (8.12.11.20060308/8.12.11) with ESMTP id pBLDVAMt012271 for ; Wed, 21 Dec 2011 07:31:10 -0600 Received: from dhcp-rmdc-twvpn-1-vpnpool-10-159-20-157.vpn.oracle.com (/10.159.20.157) by default (Oracle Beehive Gateway v4.0) with ESMTP ; Wed, 21 Dec 2011 05:31:10 -0800 Message-ID: <4EF1DF9C.4030808@oracle.com> Date: Wed, 21 Dec 2011 05:31:08 -0800 From: Rick Hillegas User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US; rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11 MIME-Version: 1.0 To: derby-user@db.apache.org Subject: Re: UPDATE FROM JOIN References: <32989399.post@talk.nabble.com> <4EEF43E1.7010409@oracle.com> <33005383.post@talk.nabble.com> <4EEFA2FA.2090801@oracle.com> <33016245.post@talk.nabble.com> In-Reply-To: <33016245.post@talk.nabble.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Source-IP: acsinet22.oracle.com [141.146.126.238] X-Auth-Type: Internal IP X-CT-RefId: str=0001.0A090202.4EF1DFA0.004E,ss=1,re=0.000,fgs=0 X-Virus-Checked: Checked by ClamAV on apache.org 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 >> >>