updateing table from a agregate join that includes the table trying to update
Posted on 2007-07-31
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, COUNT(*) AS Expr1, dbo.Tik.TikDate, dbo.Tik.TikPcs,
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.TikDate, dbo.Tik.TikPcs, dbo.TikDet.TikID,
HAVING (dbo.TikDet.TikDetOnInvoice = 1) AND (COUNT(*) <> dbo.Tik.TikPcs) AND (dbo.Tik.TikLot = 0)
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!