Link to home
Start Free TrialLog in
Avatar of zberg007
zberg007

asked on

Update Query from Multiple Tables

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.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zberg007
zberg007

ASKER

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'