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

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 Received on Thu Jun 29 1995 - 00:00:00 CEST