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
