Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Update one table by another

Re: Update one table by another

From: Rudy Zung <zungr_at_prograph-inc.com>
Date: Wed, 24 Mar 1999 16:23:25 -0500
Message-ID: <7dbl2l$u30$1@mailhost.prograph-inc.com>

Surfer Netzbetrieb wrote:   

>Hello,
>
>how can I update a colum in one table by another table's column in
SQL
>(same key)?
>
>primary-key: my_ID
>
>
> TableA TableB
> ===== ======
> my_ID my_ID
> old_field <<= new_field
>UPDATE !
>
>
>My first tries are very complex:
>
> update TableA
> set old_field =
> (Select new_field from TableB
> where my_ID in
> (Select my_ID from TableA aliasTabA where
>aliasTabA.my_ID = TableA.my_ID))

You're barking up a close tree but not exactly the right tree: update TableA a

   set old_field = (select new_field

                     from TableB b
                     where a.my_ID = b.my_ID)

I hope I got the syntax correct. The key point to remember when doing an update and setting a field to a subquery is that the subquery is allowed to return only 1 record for that query; in the above, we are limiting it to 1 record by specifying a match on its PK my_ID to match the my_ID of the table to be updated.

...Ru Received on Wed Mar 24 1999 - 15:23:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US