Need help in Updating multiple rows

Hi,

New to writing sql script

I get this error in my sql script

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.

I want to write a single sql script which will update column 1 (FK)
in table A with column 1 (PK) in table B

Here's an example of what I need to do

Table DATA_A
-------------
column C1 (Foreign Key)
11
11
11
22
22
22
33
33
33
33
44
55

Table DATA_B
------------
column C1 (Primary Key)  Column C2      Column C3
11                       ABC           NULL
12                       ABC         2004-12-12
22                       EFG           NULL
23                       EFG         2003-12-12
33                       HIJ           NULL
34                       HIJ         2003-12-12
44                       KLM         2005-02-02
55                       JJJ           NULL

I need to update Table DATA_A set column C1 with 11 data to point to
Table DATA_B column C1 with 12 data.  Currently, the problem is the
Table DATA_A Column C1 is pointing to the wrong primary key which has
NULL data in COLUMN C3. I need to point to the correct Primary Key with
Date filled in Column 3.  The two primary key is tied together by
column C3.

Here's my SQl script

UPDATE DATA_A SET C1 =
(SELECT C1 FROM DATA_B
WHERE C2 in
(SELECT B1.C2 FROM DATA_B B1
WHERE EXISTS
(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)
AND EXISTS
(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)
AND B2.C2 = B1.C2
GROUP BY B1.C2
HAVING COUNT(B1.C2) = 2)
AND C3 IS NOT NULL)
WHERE
(SELECT C1 FROM DATA_B
WHERE C2 in
(SELECT B1.C2 FROM DATA_B B1
WHERE EXISTS
(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)
AND EXISTS
(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)
AND B2.C2 = B1.C2
GROUP BY B1.C2
HAVING COUNT(B1.C2) = 2)
AND C3 IS NULL)

Thanks - Been struggle at this for a while
MLR

On 31 Mar 2005 08:44:54 -0800, mlrehberg@yahoo.com wrote:

 (snip)
>  I want to write a single sql script which will update column 1 (FK)
>in table A with column 1 (PK) in table B
>
>Here's an example of what I need to do
 (snip)
>I need to update Table DATA_A set column C1 with 11 data to point to
>Table DATA_B column C1 with 12 data.  Currently, the problem is the
>Table DATA_A Column C1 is pointing to the wrong primary key which has
>NULL data in COLUMN C3. I need to point to the correct Primary Key with
>Date filled in Column 3.  The two primary key is tied together by
>column C3.
(snip)

Hi MLR,

Try if this works:

UPDATE Data_A
SET    C1 = (SELECT     new.C1
FROM       Data_B AS curr
INNER JOIN Data_B AS new
ON   new.C2 = curr.C2
AND  new.C3 IS NOT NULL
WHERE      curr.C1 = Data_A.C1)

The above is untested. If you prefer a tested solution, then please post
a script with CREATE TABLE and INSERT statements, plus the output you
require. See www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)