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 Mon, 06 Feb 2012 20:51:49 GMT
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
>


Mime
View raw message