Re: Update multiple columns from another table

In article <39C056D1.546F_at_maxinter.net>,   cschang <cschang_at_maxinter.net> wrote:
> Anyone has experience of this? a newbie wants to learn. I have
 tried
> Update Atable a
> set ( col1, col2, col3 )
> = ( select col1, col2, col3
> from Btable
> where col4 = a.col4
> )
> where col5= "val"
>
> I have put index on unique col4 on both tables. however not only the
> return was very very slow, the number of updated row are more than
> supposed to. ( ex. Atable has 20K row, Btable has 5k rows . The
 total
> rows updated are 20K rahter 5K. Somthing is not right). Appreciate
> anyone can help.
>
> C Chang
>

Rows that have no match are updated to null as you requested. To only update those rows that have a corresponding set of values in B you need to add:

and (col1, col2, col3) in (select col1, col2, col3

                             from TableB
                            where tableb.col4 = TableA.col4 )

to your where clause condition list.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.