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:48:04 GMT
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. 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