Thread: SQL subquery question

I think this can be done with one SQL statement, but I'm not sure.

I have two tables: table t contains key k, another field f, and a bunch
of other stuff.

In a poor design decision, table tsubset contains a small number of
"pointers" to t.  I should have used the k column; instead I used the f
column (it is unique, but not the primary key).

I want to fix this.

I altered tsubset to have two columns, f and k, where k will be a
foreign key referencing t(k).

I now need to copy all the k values from t to tsubset.

I think I want to do something like this:

foreach f in tsubset
  update tsubset set k=(select k from t, tsubset where t.f=f);
end

Can this be done with one SQL statement?

Rick Schumeyer wrote:
> foreach f in tsubset
>  update tsubset set k=(select k from t, tsubset where t.f=f);
> end
>
> Can this be done with one SQL statement?

I think you mean
update tsubset set k = t.k from t where t.f = f;

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Thanks for the suggestion...it needed only one small change:

 update tsubset set k = t.k from t where t.f=tsubset.f;

Thanks!

Alban Hertroys wrote:
> Rick Schumeyer wrote:
>
>> foreach f in tsubset
>>  update tsubset set k=(select k from t, tsubset where t.f=f);
>> end
>>
>> Can this be done with one SQL statement?
>>
>
> I think you mean
> update tsubset set k = t.k from t where t.f = f;
>
>