updateing sql table from a joined aggregate view

i have 2 tables

item table
fields     ID,cycles,LastDelivered... and other unrelated data

LineItem table
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.
CASorterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
WITH cte AS
(
   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
)
UPDATE Item
SET Cycles = cte.cnt,
   LastDelivered = cte.LastTouched
FROM Item
INNER JOIN cte
   ON Item.ID = cte.ID
WHERE Item.Cycles <> cte.cnt
   OR Item.LastDelivered <> cte.LastTouched
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CASorterAuthor Commented:
sweet....


i was in the midst of creating a temp table...   and updating from that temp table to the item table...


thanks,
much more elegant!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.