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: Porting to standard SQL
Date Tue, 07 Feb 2012 17:11:31 GMT
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?


Mime
View raw message