i have 2 tables
fields ID,cycles,LastDelivered... and other unrelated data
fields LineItemID, ItemID, dateUsed... and other unrelated data
i join them like this
SELECT Item.ID , COUNT(*) AS cnt, item.Cycles, Item.LastDelivered ,
MAX(LineItem.dateused) AS lasttouched
FROM item INNER JOIN
LineItem ON item.ID = LineItem.ItemID
GROUP BY item.ID, item.Cycles, item.LastDelivered
i get data like this
ID cnt cycles lastdeliverd lasttouched
00802E8B1B 4 NULL NULL 2014-03-07 06:01:39.610
0005DF21FD 5 NULL NULL 2014-03-13 13:12:03.000
000ED859B4 2 NULL NULL 2014-02-05 07:49:24.000
00105D7F77 4 NULL NULL 2014-02-18 05:54:55.000
the relation ship is one Item record to many LineItem records
what i want to do is to update the cycles field and LastDelivered field in the Item table with the count and last touched fields for each ID in the result set.
i created a view with the above info, but.... if i try and update the view (a trick i use when updating a join, cuz its easier for me) it says i cant because the underlying view contains an agrigate which it does....
i am *hoping* to be able to just do it in a query... but would take a sproc if neccassary.
this is a one time run for this data, because we are adding the cycles and lastDeliverd fields and will be updating them going forward.