Solved

Update Query from Multiple Tables

Posted on 2011-03-03
3
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 the fundamental information of how to create a table.

691 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