Re: Updating one table with values from another

The meaning of the message you get is that at least one row in table TABLEA has multiple corresponding rows in table TABLEB when using the sub-query's where clause
You would like to update TABLEA with the corresponding value in TABLEB: this is only possible when the relation between TABLEA and TABLEB is 1 to 1. Check your data, or enhance your query to insure the where clause build a 1:1 relation. (eg if it's normal to have a 1:n relation, reduce it using rownum=1 th enhance the where clause)

NEO 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 )
>
> 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/
Received on Mon Feb 12 2001 - 16:50:40 CST