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
