CASorter
asked on
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.
item table
fields ID,cycles,LastDelivered...
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i was in the midst of creating a temp table... and updating from that temp table to the item table...
thanks,
much more elegant!!