Microsoft SQL Server forum - Need help in Updating multiple rows

Author

Need help in Updating multiple rows

mlrehberg@yahoo.com

2005-03-31, 8:03 pm

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

2005-03-31, 8:03 pm

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)

Sponsored Links