Re: UPDATE A with B

In article <1993Feb12.183538.1002_at_newsserver.rrzn.uni-hannover.de>, rusche_at_iw.uni-hannover.de (Henrik Rusche) writes:
>
> Hey,
>
> here's a problem I would like have to solve only with SQL.
> ----
> With PL/SQL it's simple.
>
> There are two tables (A,B) with the same structure. Let's say
>
> nr number (3), primary key
> text char (50)
>
>
> Now I want to update A with B following this schema :
>
> A.Text = B.Text if A.nr = B.nr
>
>
>
>
> My first try :
>
> UPDATE A
> SET A.Text = B.Text
> WHERE A.Nr = B.Nr;
>
> But that's no solution. Is there a solution - If not why ??????
>

UPDATE a

   SET text=(SELECT text

               FROM b
              WHERE b.nr=a.nr)
 WHERE EXISTS (SELECT 1
                 FROM b
                WHERE b.nr=a.nr)

kind regards

Mike

                                                                          ^
 Federal Ministry of Science and Research                               B | M
<-------------------------------------------------------------------------+---->
 Computer Center                                                        W | F
                                                                          |
 Ing. Klaus-Michael Hatzinger       mail: hatzinger_m_at_bmwf1f.bmwf.gv.at   |
 Bankgasse 1/209                   phone: 0043-222-53120/5188             |
 1014 Vienna, Austria                fax: 0043-222-53120/5155             V
================================================================================