Updating multiple rows using a subquery in SQL

Question

I have a summary table return data from a master table. I am trying to update some of the summary data from values in the master table as such:

update #summary
  set TopSpeed = CD.TopSpeed, SpeedTime = CD.TimeSent, SpeedDriver = CD.Driver
from
(
  select top 1 TopSpeed, TimeSent, Driver
  from CarData
  where CarData.VehicleId = #summary.VehicleId 
  and CarData.TimeSent between #summary.Start and #summary.Stop
  order by CarData.TopSpeed desc, TimeSent desc
) as CD

The #summary temporary table is creating summarised data about trips undertaken by a car. There can be multiple trips for each car with each trip having a start and stop time. The CarData table contains all the detailed car data like speed and position etc.

Please can you help?

Thanks, Robert

Answer 1

Try this:

UPDATE
    #summary
SET
    #summary.TopSpeed = CarData.TopSpeed ,
    #summary.SpeedTime = CarData.SpeedTime 
    ...
FROM
    #summary
INNER JOIN
    CarData
ON
    #summary.id = CarData.id AND (CarData.TimeSent BETWEEN #summary.Start AND #summary.Stop)