Help with Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Update Query

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-05 : 08:14:25

I am getting the following error when trying to perform this update query: '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.'

My update query shoud update all records in a temp table with a address data from other tables.

update #Address_Compare
Set CheckingAddress =
(Select
'Checking Service Billing Address Data' = Case
When Svc.ServiceDescription = 'Checking' and Addr.Line2 = '' then
coalesce(rtrim(Addr.Line1) + ', ','') + coalesce(rtrim(Addr.City) + ', ','') + coalesce(rtrim(Addr.State) + ', ','') + coalesce(rtrim(Addr.Zip) + ', ','') + coalesce(rtrim(Addr.Country),'')
else
coalesce(rtrim(Addr.Line1) + ', ','') + coalesce(rtrim(Addr.Line2) + ', ','') + coalesce(rtrim(Addr.City) + ', ','') + coalesce(rtrim(Addr.State) + ', ','') + coalesce(rtrim(Addr.Zip) + ', ','') + coalesce(rtrim(Addr.Country),'')
End
from CustomerService CustSer, Customer c1, Address Addr, Services Svc, Customer cust
where CustSer.CoID = Cust.CoID
and CustSer.AddressID = Addr.AddressID
and CustSer.ServiceID = Svc.ServiceID
and c1.coid = CustSer.BillingHomeOfficeCoID
and CustSer.billinghomebranchind = 'H'
and Svc.ServiceDescription = 'Checking')

brianjensen
Starting Member

22 Posts

Posted - 2008-11-05 : 09:17:49

Well it says that one of your queries return more than one row, and therefore you cannot compare the result to one value.
So either your data set is faulty or you may be missing an identifyer in a WHERE clause that makes the returned value distinct.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:53:13

what you're trying to do is update all values of your table column with a resultset which returns more than 1 value. I think you should be taking a join with subquery on related columns and then do update. Not sure unless we know your correct requirement. If you can illustrate what you want with some sample data and o/p that would be great.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-05 : 12:06:39

Hi visakh16, I ended up just going with some additional temporary tables to acomplish what I needed. A few more steps than I would have liked but in the end, I got what I needed.

Thanks for your help.
This forum is fantastic!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 12:08:31

No problem. You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources