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, COUNT(*) AS Expr1, dbo.Tik.TikDate, dbo.Tik.TikPcs,
                      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.TikDate, dbo.Tik.TikPcs, dbo.TikDet.TikID,
                      dbo.Tik.TikLot
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!
CASorterAsked:
Who is Participating?
 
mankowitzConnect With a Mentor Commented:
can you do this?

update d set dbo.tik.tikpcs = expr1
from dbo.tik d join
(SELECT  dbo.TikDet.TikDetOnInvoice, COUNT(*) AS Expr1, dbo.Tik.TikDate, dbo.Tik.TikPcs,
                      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.TikDate, dbo.Tik.TikPcs, dbo.TikDet.TikID,
                      dbo.Tik.TikLot
HAVING      (dbo.TikDet.TikDetOnInvoice = 1) AND (COUNT(*) <> dbo.Tik.TikPcs) AND (dbo.Tik.TikLot = 0)) j on j.tikid= d.tikid
0
 
CASorterAuthor Commented:
getting this message

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.
0
 
CASorterAuthor Commented:
how do i close this call without awarding points?

i solved my own problem.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.