updateing table from a agregate join that includes the table trying to update
Complete brain fart here.
what i am trying to accomplish is to use an aggregate query with a join to update a field in one of the tables in that same join.
here is the join. it looks for oninvoice flags that are turned on and counts up the total for each tikID
SELECT dbo.TikDet.TikDetOnInvoice
dbo.TikDet.TikID, dbo.Tik.TikLot
FROM dbo.TikDet INNER JOIN
dbo.Tik ON dbo.TikDet.TikID = dbo.Tik.TikID
WHERE (dbo.Tik.tikismat = 0)
GROUP BY dbo.TikDet.TikDetOnInvoice
dbo.Tik.TikLot
HAVING (dbo.TikDet.TikDetOnInvoic
the problem is that the dbo.tik.tikpcs field is wrong (this is an imported table and the data was sent to me incorrectly and i am trying to clean it up.) it needs to contain the count of oninvoice and doesnt.
so what i need to do is use the above query to update the dbo.tik.tikpcs field in the dbo.tik table. setting dbo.tik.tikpcs = expr1
i am flitting around how to get it but am getting to the thrashing point and need a new set of eyes.. thanks for any help!
what i am trying to accomplish is to use an aggregate query with a join to update a field in one of the tables in that same join.
here is the join. it looks for oninvoice flags that are turned on and counts up the total for each tikID
SELECT dbo.TikDet.TikDetOnInvoice
dbo.TikDet.TikID, dbo.Tik.TikLot
FROM dbo.TikDet INNER JOIN
dbo.Tik ON dbo.TikDet.TikID = dbo.Tik.TikID
WHERE (dbo.Tik.tikismat = 0)
GROUP BY dbo.TikDet.TikDetOnInvoice
dbo.Tik.TikLot
HAVING (dbo.TikDet.TikDetOnInvoic
the problem is that the dbo.tik.tikpcs field is wrong (this is an imported table and the data was sent to me incorrectly and i am trying to clean it up.) it needs to contain the count of oninvoice and doesnt.
so what i need to do is use the above query to update the dbo.tik.tikpcs field in the dbo.tik table. setting dbo.tik.tikpcs = expr1
i am flitting around how to get it but am getting to the thrashing point and need a new set of eyes.. thanks for any help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
how do i close this call without awarding points?
i solved my own problem.
i solved my own problem.
ASKER
Cannot use the column prefix 'dbo.tik'. This must match the object in the UPDATE clause 'd'.
i worked out a brute force method....
what i did was jam the join query into a temp table and then update based on that.... but there must be a more elegant way to do it.