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 TXVanguard <br...@lmco.com> on 2012/02/02 23:53:48 UTC
Porting to standard SQL
I am working on a project to port SQL code written for Microsoft Access to
SQL code that works in JavaDB.
The following SQL statement works in Access:
UPDATE T1 INNER JOIN T2 ON (T1.A= T2.A) SET T2.B = T1.B
but when I try it in JavaDB, I get the following error:
Error code -1, SQL state 42X01: Syntax error: Encountered "INNER" at line 1,
column 12.
A coworker suggested this:
UPDATE T2 SET T2.B = T1.B FROM T2 INNER JOIN T1 ON T1.A = T2.A
but the FROM clause is not supported by JavaDB.
Can anyone tell me how to rewrite the first SQL statement to work in JavaDB?
Note: I'm new to SQL and am learning as quickly as I can, but I have
real-world SQL problems like the one-above that can't wait for me to become
an expert. :)
--
View this message in context: http://old.nabble.com/Porting-to-standard-SQL-tp33253800p33253800.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Porting to standard SQL
Posted by TXVanguard <br...@lmco.com>.
Rick, you've been very patient and helpful. I'm still not sure I understand
the complexities of what's going on, but you've shed a lot of light on the
subject. Thanks for your help.
Rick Hillegas-3 wrote:
>
> Hm, again I'm not an expert on Access syntax. I think that following the
> pattern of the previous ported statements may help:
>
>
--
View this message in context: http://old.nabble.com/Porting-to-standard-SQL-tp33253800p33280340.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Porting to standard SQL
Posted by Rick Hillegas <ri...@oracle.com>.
On 2/7/12 8:09 AM, TXVanguard wrote:
> Rick,
>
> I've discovered a few more wrinkles in some of the UPDATE/INNER JOIN states
> I have to port. Here's an example:
>
> UPDATE T1 INNER JOIN (T2a INNER JOIN T2b ON T2a.B = T2b.B) ON T1.B = T2b.B
> SET T2a.D = true WHERE (T2b.E=true AND NOT (T1.F=2 AND (T2b.G=2 OR
> T2b.G=1)));
Hi John,
Hm, again I'm not an expert on Access syntax. I think that following the
pattern of the previous ported statements may help:
1) Find the table which is being updated (in this case it is T2).
2) Write an UPDATE statement against that table. The SET clause should
set the column(s) of the updated table to the result of subquery(ies).
a) The joins go into the subquery(ies). In this case, you have a join of
T2 x T2 x T1.
b) The WHERE clause which follows the original SET clause should be
converted into a WHERE clause on the new UPDATE statement but using an
EXISTS subquery against whatever join is being performed in the original
WHERE clause.
But this is tricky since I don't know what the Access query is trying to
do. I would also recommend the following lines of attack:
A) Verify that your Derby UPDATE produces the same results as your
Access UPDATE on the same data set.
B) See if you can find any comments around the original Access queries
explaining at a high level what they are trying to accomplish.
Sorry that I can't give you any more definitive advice. Maybe some
better advice will come from someone who understands the Access SQL dialect.
Hope this helps,
-Rick
> Note that the "table" on the right of the first INNER JOIN is not a table,
> rather the result of another INNER JOIN. Is there any way to rewrite this
> in standard SQL?
Re: Porting to standard SQL
Posted by TXVanguard <br...@lmco.com>.
Rick,
I've discovered a few more wrinkles in some of the UPDATE/INNER JOIN states
I have to port. Here's an example:
UPDATE T1 INNER JOIN (T2a INNER JOIN T2b ON T2a.B = T2b.B) ON T1.B = T2b.B
SET T2a.D = true WHERE (T2b.E=true AND NOT (T1.F=2 AND (T2b.G=2 OR
T2b.G=1)));
Note that the "table" on the right of the first INNER JOIN is not a table,
rather the result of another INNER JOIN. Is there any way to rewrite this
in standard SQL?
--
View this message in context: http://old.nabble.com/Porting-to-standard-SQL-tp33253800p33279676.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Porting to standard SQL
Posted by Rick Hillegas <ri...@oracle.com>.
On 2/6/12 2:29 PM, TXVanguard wrote:
>
>
> Rick Hillegas-3 wrote:
>>> Hope this helps,
>>> -Rick
> Rick, thanks for all your help. The "SELECT DISTINCT" syntax seems to work.
>
> Here's one more wrinkle I discovered:
>
> I have several lines that look like this:
>
> UPDATE T1 INNER JOIN T2 ON (T1.A= T2.A) SET T2.B = T1.B WHERE (T1.C = TRUE
> AND T2.C = 5)
I'm not an expert in Access syntax. I'm reluctant to speculate about
what that statement does or whether its behavior is well defined.
> (The only difference form my initial question is the presence of the WHERE
> clause.
>
> Is this the correct way to rewrite it (below)?
>
> UPDATE T2 SET B = (SELECT DISTINCT(B) FROM T1 WHERE T1.a = T2.a) WHERE (T1.C
> = TRUE AND T2.C>= 1)
That statement won't parse on Derby because the "t1.c = true"
restriction falls outside the subquery and cannot be evaluated just by
scanning the table which is being updated.
The following statements at least get past the Derby parser:
update t2 set b = (select distinct(b) from t1 where t1.a = t2.a and t1.c
= true and t2.c >= 1);
update t2 set b = (select distinct(b) from t1 where t1.a = t2.a and t1.c
= true) where t2.c >= 1;
The following statement may take longer to evaluate but may capture the
sense of the Access statement better. Again, I can't say because I don't
understand the Access statement:
update t2 set b = (select distinct(b) from t1 where t1.a = t2.a)
where exists (select b from t1 where t1.a = t2.a and t1.c = true and
t2.c >= 1);
Hope this helps,
-Rick
Re: Porting to standard SQL
Posted by TXVanguard <br...@lmco.com>.
Dag H. Wanvik-2 wrote:
>
> I don't believe Derbys support updating JOINs, which is why you need to
> rewrite the query as suggested earlier in this thread.
>
Right. I understand that I need to rewrite the query as suggested; I was
trying to find out how to correctly incorporate the WHERE clause into the
rewritten query.
--
View this message in context: http://old.nabble.com/Porting-to-standard-SQL-tp33253800p33279896.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Porting to standard SQL
Posted by "Dag H. Wanvik" <da...@oracle.com>.
TXVanguard <br...@lmco.com> writes:
> I have several lines that look like this:
>
> UPDATE T1 INNER JOIN T2 ON (T1.A= T2.A) SET T2.B = T1.B WHERE (T1.C = TRUE
> AND T2.C = 5)
I don't believe Derbys support updating JOINs, which is why you need to
rewrite the query as suggested earlier in this thread.
See Derby's allowed syntax for UPDATE her:
http://db.apache.org/derby/docs/10.8/ref/rrefsqlj26498.html
Thanks,
Dag
Re: Porting to standard SQL
Posted by TXVanguard <br...@lmco.com>.
Rick Hillegas-3 wrote:
>
>> Hope this helps,
>> -Rick
>
Rick, thanks for all your help. The "SELECT DISTINCT" syntax seems to work.
Here's one more wrinkle I discovered:
I have several lines that look like this:
UPDATE T1 INNER JOIN T2 ON (T1.A= T2.A) SET T2.B = T1.B WHERE (T1.C = TRUE
AND T2.C = 5)
(The only difference form my initial question is the presence of the WHERE
clause.
Is this the correct way to rewrite it (below)?
UPDATE T2 SET B = (SELECT DISTINCT(B) FROM T1 WHERE T1.a = T2.a) WHERE (T1.C
= TRUE AND T2.C >= 1)
--
View this message in context: http://old.nabble.com/Porting-to-standard-SQL-tp33253800p33275262.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Porting to standard SQL
Posted by Rick Hillegas <ri...@oracle.com>.
On 2/6/12 12:48 PM, Rick Hillegas wrote:
> On 2/6/12 12:36 PM, TXVanguard wrote:
>>
>>
>> Rick Hillegas-3 wrote:
>>> The Access update could change the target row twice. No one will notice
>>> if both matching rows from T1 have the same value in column B. Or...not
>>> many people will notice if the rows from the join always come back in
>>> the same order so that the same value of B always wins. It might be
>>> useful to run the join outside the update and see what it returns on
>>> Access and Derby.
>>>
>>>
>> I ran the INNER JOINs in both Access and Derby as you suggested. Both
>> seem
>> to return the same result.
>>
>> Column T2.A is the primary key, so every value is unique. Column T1.A
>> has
>> many rows that have the same value as each other, but each value that
>> occurs
>> in T1.A matches one of the values in T2.A.
>>
>> It appears that whenever T1.A has many rows that share the same
>> value, the
>> value in T1.B is always the same. (Maybe this is indicative of
>> suboptimal
>> design.)
>>
>> Could I rewrite my original SQL statement in the way suggested by Bryan
>> Pendleton-3, but modify it somehow to use only the "first" value of
>> T1.B?
>>
> If all matching rows have the same value for B, then you can modify
> Bryan's query slightly, making it a SELECT DISTINCT rather than a DISTINCT
Make that a SELECT DISTINCT rather than a plain SELECT.
> . That should do the trick. The following script shows this behavior:
>
> connect 'jdbc:derby:memory:db;create=true';
>
> create table t1( a int, b int );
> create table t2( a int, b int );
>
> insert into t1( a, b ) values ( 1, 2 ), ( 1, 2 );
> insert into t2( a, b ) values ( 1, 1 );
>
> -- fails
> update t2 set b = (select b from t1 where t1.a = t2.a);
>
> -- works
> update t2 set b = (select distinct( b ) from t1 where t1.a = t2.a);
>
> Hope this helps,
> -Rick
>
Re: Porting to standard SQL
Posted by Rick Hillegas <ri...@oracle.com>.
On 2/6/12 12:36 PM, TXVanguard wrote:
>
>
> Rick Hillegas-3 wrote:
>> The Access update could change the target row twice. No one will notice
>> if both matching rows from T1 have the same value in column B. Or...not
>> many people will notice if the rows from the join always come back in
>> the same order so that the same value of B always wins. It might be
>> useful to run the join outside the update and see what it returns on
>> Access and Derby.
>>
>>
> I ran the INNER JOINs in both Access and Derby as you suggested. Both seem
> to return the same result.
>
> Column T2.A is the primary key, so every value is unique. Column T1.A has
> many rows that have the same value as each other, but each value that occurs
> in T1.A matches one of the values in T2.A.
>
> It appears that whenever T1.A has many rows that share the same value, the
> value in T1.B is always the same. (Maybe this is indicative of suboptimal
> design.)
>
> Could I rewrite my original SQL statement in the way suggested by Bryan
> Pendleton-3, but modify it somehow to use only the "first" value of T1.B?
>
If all matching rows have the same value for B, then you can modify
Bryan's query slightly, making it a SELECT DISTINCT rather than a
DISTINCT. That should do the trick. The following script shows this
behavior:
connect 'jdbc:derby:memory:db;create=true';
create table t1( a int, b int );
create table t2( a int, b int );
insert into t1( a, b ) values ( 1, 2 ), ( 1, 2 );
insert into t2( a, b ) values ( 1, 1 );
-- fails
update t2 set b = (select b from t1 where t1.a = t2.a);
-- works
update t2 set b = (select distinct( b ) from t1 where t1.a = t2.a);
Hope this helps,
-Rick
Re: Porting to standard SQL
Posted by TXVanguard <br...@lmco.com>.
Rick Hillegas-3 wrote:
>
> The Access update could change the target row twice. No one will notice
> if both matching rows from T1 have the same value in column B. Or...not
> many people will notice if the rows from the join always come back in
> the same order so that the same value of B always wins. It might be
> useful to run the join outside the update and see what it returns on
> Access and Derby.
>
>
I ran the INNER JOINs in both Access and Derby as you suggested. Both seem
to return the same result.
Column T2.A is the primary key, so every value is unique. Column T1.A has
many rows that have the same value as each other, but each value that occurs
in T1.A matches one of the values in T2.A.
It appears that whenever T1.A has many rows that share the same value, the
value in T1.B is always the same. (Maybe this is indicative of suboptimal
design.)
Could I rewrite my original SQL statement in the way suggested by Bryan
Pendleton-3, but modify it somehow to use only the "first" value of T1.B?
--
View this message in context: http://old.nabble.com/Porting-to-standard-SQL-tp33253800p33274358.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Porting to standard SQL
Posted by Rick Hillegas <ri...@oracle.com>.
On 2/6/12 10:22 AM, TXVanguard wrote:
>
>
> Rick Hillegas-3 wrote:
>>
>> This means that more than 1 row is returned by the subquery. That, in
>> turn, suggests that the query, which succeeds for you on another
>> database, may not be behaving in a predictable way there. Since more
>> than one row qualifies to drive the update, which row do you want? Is
>> there some other restriction which you can put in the subquery to
>> guarantee that you get one, predictable row?
>>
>> Hope this helps,
>> -Rick
>>
>>
> I wonder why this doesn't cause a problem for Access?
The Access update could change the target row twice. No one will notice
if both matching rows from T1 have the same value in column B. Or...not
many people will notice if the rows from the join always come back in
the same order so that the same value of B always wins. It might be
useful to run the join outside the update and see what it returns on
Access and Derby.
Hope this helps,
-Rick
Re: Porting to standard SQL
Posted by TXVanguard <br...@lmco.com>.
Rick Hillegas-3 wrote:
>
>
> This means that more than 1 row is returned by the subquery. That, in
> turn, suggests that the query, which succeeds for you on another
> database, may not be behaving in a predictable way there. Since more
> than one row qualifies to drive the update, which row do you want? Is
> there some other restriction which you can put in the subquery to
> guarantee that you get one, predictable row?
>
> Hope this helps,
> -Rick
>
>
I wonder why this doesn't cause a problem for Access?
--
View this message in context: http://old.nabble.com/Porting-to-standard-SQL-tp33253800p33273439.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Porting to standard SQL
Posted by Rick Hillegas <ri...@oracle.com>.
On 2/6/12 7:43 AM, TXVanguard wrote:
>
> Bryan Pendleton-3 wrote:
>>
>> Perhaps something like:
>>
>> update t2 set b = (select b from t1 where t1.a = t2.a)
>>
>>
> I tried your suggestion, but now I get the error message:
>
> "Scalar subquery is only allowed to return a single row."
>
> Any other suggestions?
This means that more than 1 row is returned by the subquery. That, in
turn, suggests that the query, which succeeds for you on another
database, may not be behaving in a predictable way there. Since more
than one row qualifies to drive the update, which row do you want? Is
there some other restriction which you can put in the subquery to
guarantee that you get one, predictable row?
Hope this helps,
-Rick
Re: Porting to standard SQL
Posted by TXVanguard <br...@lmco.com>.
Bryan Pendleton-3 wrote:
>
>
> Perhaps something like:
>
> update t2 set b = (select b from t1 where t1.a = t2.a)
>
>
I tried your suggestion, but now I get the error message:
"Scalar subquery is only allowed to return a single row."
Any other suggestions?
--
View this message in context: http://old.nabble.com/Porting-to-standard-SQL-tp33253800p33272288.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Porting to standard SQL
Posted by Bryan Pendleton <bp...@gmail.com>.
On 02/02/2012 02:53 PM, TXVanguard wrote:
> UPDATE T1 INNER JOIN T2 ON (T1.A= T2.A) SET T2.B = T1.B
Perhaps something like:
update t2 set b = (select b from t1 where t1.a = t2.a)
thanks,
bryan