Link to home
Start Free TrialLog in
Avatar of armgon
armgonFlag for United States of America

asked on

MS SQL Update query with connected table data

I need to update fields in table  ' PIDetail'
using data from this query:

select distinct a.invtid, sum (a.qty) as 'qtytoupdate'
from ZZZ_CycleCount_update a, PIDetail b
where a.invtid = b.invtid
and b.PIID='00632'and b.WhseLoc='1'
group by a.invtid

These are the  fields in the 'PIDetail' table and need to be updated accordingly:
PIDetail.Status='E'

PhysQty = ZZZ_CycleCount.('qtytoupdate' column returned from the above select query)

Extcostvariance = ZZZ_CycleCount.('qtytoupdate' column returned from the above select query) minus PIDetail.Bookqty all multiplied by PIDetail.UnitCost

Any assistance you could provide to create this update query would be truly appreicated.
Avatar of JesterToo
JesterToo
Flag of United States of America image

Save the values from the first query that you need to work with then perform the calculations saving the results in new variables and finally write the update query using the values you have saved.
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America 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
Avatar of armgon

ASKER

Mike the CTE did the trick. Thanks you.