You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by JulioSerje <js...@gmail.com> on 2011/12/16 22:42:01 UTC
UPDATE FROM JOIN
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.
--
View this message in context: http://old.nabble.com/UPDATE-FROM-JOIN-tp32989399p32989399.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: UPDATE FROM JOIN
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Rick Hillegas <ri...@oracle.com> writes:
> 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.
We also have https://issues.apache.org/jira/browse/DERBY-4074, which
requests syntax that provides functionality similar to UPDATE ... FROM.
--
Knut Anders
Re: UPDATE FROM JOIN
Posted by Rick Hillegas <ri...@oracle.com>.
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
>>
>>
Re: UPDATE FROM JOIN
Posted by JulioSerje <js...@gmail.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.
Re: UPDATE FROM JOIN
Posted by Rick Hillegas <ri...@oracle.com>.
On 12/19/11 12:24 PM, JulioSerje wrote:
>
>
> 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?
>
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
Re: UPDATE FROM JOIN
Posted by JulioSerje <js...@gmail.com>.
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.
Re: UPDATE FROM JOIN
Posted by Rick Hillegas <ri...@oracle.com>.
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