Solved

Update Query from Multiple Tables

Posted on 2011-03-03
3
274 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

821 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