Re: Update one table by another

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