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 

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,

View raw message