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

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
:

You can update columns with data from another table by using a subquery in the SET part of a UPDATE statement. You can't however update multiple tables with one UPDATE statement. What you are looking for seems to me to be:

UPDATE t1
SET t1.value = (SELECT t2.value FROM t2 WHERE t2.Code = t1.Code) WHERE t1.Code IN
  (SELECT t2.code FROM t2)

If you leave out the WHERE part of the UPDATE, code values that occur in T1 but not in T2 will be set to NULL. Don't know if this is what you want. If not, include the WHERE.

Hope this helps.

This information is provided without any guarantee. I'm posting this as a private person and I am not representing Oracle in this matter. I work for them, but this is free advice given in my spare time. Received on Sat Jul 01 1995 - 00:00:00 CEST