Solved

Update Query from Multiple Tables

Posted on 2011-03-03
3
273 Views
Last Modified: 2012-05-11
I have this query that returns the rows that need updated:
select a.als_UINNO, b.ALS_UIN_NO
from alsUnitConvertTable a, components b, accounts c
where b.account_id = c.account_id
and c.lab_id = '224'
and a.samplePointDesc = b.COMPONENT_DESC
and a.sam_acct = b.lab_cno
and a.u_UnitNo = b.lab_unit_id
and a.u_compSerNo = b.lab_comp_id
order by b.lab_unit_id

Open in new window


I wish to populate the b.ALS_UIN_NO with the values from a.als_UINNO

I thought I could do it with something like this:

update components
set ALS_UIN_NO = (select a.als_uinno
                     from alsunitconverttable a, components b, accounts c
                     where b.account_id = c.account_id
                     and c.lab_id = '224'
                     and a.samplePointDesc = b.COMPONENT_DESC
                     and a.sam_acct = b.lab_cno
                     and a.u_UnitNo = b.lab_unit_id
                     and a.u_compSerNo = b.lab_comp_id)

But I get this error:
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.

Not sure how to structure the update statement better than what I currently have... Solution please, please.. thanks.
0
Comment
Question by:zberg007
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 35034187
this article shall solve the issue:
http://www.experts-exchange.com/A_1517.html
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 250 total points
ID: 35034197
you need to join the inner result with the outer table inorder to update on row at a time.



update components Co
set ALS_UIN_NO = (select a.als_uinno
                     from alsunitconverttable a, components b, accounts c
                     where b.account_id = c.account_id
                     and c.lab_id = '224'
                     and a.samplePointDesc = b.COMPONENT_DESC
                     and a.sam_acct = b.lab_cno
                     and a.u_UnitNo = b.lab_unit_id
                     and a.u_compSerNo = b.lab_comp_id
                     and a.column = Co.colum -- or join the other table)

0
 

Author Closing Comment

by:zberg007
ID: 35034324
I ended using both solutions to form this statement which worked:

update b
set ALS_UIN_NO = a.als_uinno
                     from components b
                     join alsunitconverttable a
                     on a.samplePointDesc = b.COMPONENT_DESC
                     and a.sam_acct = b.lab_cno
                     and a.u_UnitNo = b.lab_unit_id
                     and a.u_compSerNo = b.lab_comp_id
                     and b.lab_id = '224'
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now