• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

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!
0
CASorter
Asked:
CASorter
  • 2
1 Solution
 
mankowitzCommented:
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now