Re: SQL: Update .. from .. where ..

In article <DAxp22.LHC_at_nntpa.cb.att.com>, james.mumper <jimm_at_cbnews.cb.att.com> wrote:
>In article <3spgce$o6p_at_charon.rutgers.edu>,
>Yin Liang <yliang_at_charon.rutgers.edu> wrote:
>>Hi, I am whether I can update a field in one table from valuse in another
>>table using a simple SQL Statement ?
>>
>>the Tables are :
>>
>> T1 T2
>> Code Code
>> Value Value
>> otherstaff..
>>
>>where Code is the PK of both tables (1 to 1)
>>
>>and I think the Update statement should be:
>>
>>Update T1 set T1.Value = T2.Value
>>From T1, T2
>>where T1.Code = T2.Code (Runs perfect in SYBASE !)
>>
>>but check the Oracel Server Language Refernece book, the Update can not
>>have a FROM clause, but then How you updating a field from another
>>table in SQL ?
>>
>>Thanks for any help
>>
>>
>>Y. Liang
>>
>
>It's very possible, you just need a subquery. Try this:
>
>Update T1 set T1.Value = (Select T2.Value
>From T2
>where T1.Code = T2.Code (Runs perfect in SYBASE !)
>
>Should work nicely, especially with an index. If you have an
>embedded base of data in the table, this will get you up to speed. But
>if not, or if you will be adding additional data to both tables, you
>should consider a trigger, so that every time T1 gets updated, deleted,
>or inserted, the corresponding record in T2 will get updated also.
>
>Jim Mumper

Man, I should really edit these damn things before I send them out. The sql statement should read as follows:

Update T1 set Value = (Select T2.Value
From T2
where T1.Code = T2.Code );

That should work nicely, not the last one. So sorry.

Jim Received on Thu Jun 29 1995 - 00:00:00 CEST