Need help in Updating multiple rows

[mlrehberg]

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

[Hugo Kornelis]

On 31 Mar 2005 08:44:54 -0800, ml*******@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)