armgon
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.('qtytoupda te' column returned from the above select query)
Extcostvariance = ZZZ_CycleCount.('qtytoupda te' 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.
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.('qtytoupda
Extcostvariance = ZZZ_CycleCount.('qtytoupda
Any assistance you could provide to create this update query would be truly appreicated.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mike the CTE did the trick. Thanks you.