Update a table from another impoting modified and new records

Question

Update a table from another impoting modified and new records

j.grimanis

SSC Eights!

Points: 852
More actions
August 28, 2009 at 6:35 am

#133269

Hi all,
I want to update a table A from another one B, impoting modified and new records from A and simultaneously keep the unmodified old records from B.
The Tsql is the following :
declare @C varchar(50)
set @C=(select t1.name from t1 inner join t2 on t1.name=t2.name)
update t1
set num = case
when name = @C
then (select t2.num from t2 inner join t1 on t1.name=t2.name )
when name<>@C
then (select num from t2)
else t1.num
end
But the result is not correct and I get message:
' Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=,........ '
I would appreciate if someone could help.
Thanks,
John

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

kramaswamy SSCoach Points: 18181 More actions · Answer 1

j.grimanis (8/28/2009)
Hi all,
I want to update a table A from another one B, impoting modified and new records from A and simultaneously keep the unmodified old records from B.
The Tsql is the following :
declare @C varchar(50)
set @C=(select t1.name from t1 inner join t2 on t1.name=t2.name)
update t1
set num = case
when name = @C
then (select t2.num from t2 inner join t1 on t1.name=t2.name )
when name@c
then (select num from t2)
else t1.num
end
But the result is not correct and I get message:
' Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=,........ '
I would appreciate if someone could help.
Thanks,
John

Your code has several problems. All those subqueries are returning multiple records; this is not allowed. A subquery can only return one record, if you're comparing it using =, etc...

You're going to have to elaborate more on what it is that you're trying to do, because your CASE statement logic doesn't really make sense.

kramaswamy SSCoach Points: 18181 More actions · Answer 2

Okay, after looking at your code a bit, is this what you're trying to accomplish:

For each record in T1

If a record exists in T2 with the same name, use T2.num

If no record exists in T2 with the same name, keep T1.num

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

You don't need a loop to handle this. Use a JOINed UPDATE statement which only handles rows with same key in both tables:

UPDATE A SET

A.Num = B.Num

FROM TableA A

JOIN TableB B ON A.Name = B.Name