Re: Updating one table with values from another

From: Alex Filonov <afilonov_at_pro-ns.net>
Date: Mon, 12 Feb 2001 04:44:03 GMT
Message-ID: <967pmi$8g5$1@nnrp1.deja.com>

In article <967nqr$726$1_at_nnrp1.deja.com>, NEO <sneo_at_my-deja.com> wrote:
> Hi. I am having some trouble with what I think should be fairly
> straight forward! Maybe, I have been looking at the screen too long
:)
>
> I have two tables:
>
> TABLEA ( COLA1, COLA2 )
> TABLEB ( COLB1, COLB2 )
>
> I want to set TABLEA.COLA1 = TABLEB.COLB1 where TABLEA.COLA2 =
> TABLEB.COLB2. I tried the following:
>
> UPDATE TABLEA
> SET (COLA1) = (SELECT COLB1 FROM TABLEB
> WHERE TABLEA.COLA2 = TABLEB.COLB2 )
update tablea
set cola1 = (select colb1 from tableb

             where tableb.colb2 = tablea.cola2
               and rownum = 1) -- we want 1 row per subquery only
where cola2 in (select colb2 from tableb) -- update only those rows
                                          -- in tablea which have
                                          -- matched rows in tableb.

>
> Executing the above update returns an error: single-row subquery
> returns more than one row.
>
> The error is correct but, I am not sure how to re-write the SQL such
> that only those rows in TABLEA get updated that match the WHERE
clause.
>
> Any help is appreciated.
>
> Thanks in advance.
>
> Best Regards.
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Sun Feb 11 2001 - 22:44:03 CST