Updating one table with values from another

From: NEO <sneo_at_my-deja.com>
Date: Mon, 12 Feb 2001 04:12:14 GMT
Message-ID: <967nqr$726$1@nnrp1.deja.com>

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 Sun Feb 11 2001 - 22:12:14 CST