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?
