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 14:06:25 GMT
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


Mime
View raw message