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.

It says that the #summary.VehicleId, #summary.Start and #summary.Stop inside the subquery cannot be found.

I am using MS SQL Server 2000.

Please can you help?

Thanks, Robert

Answer 1

I believe you have to move the where condition to the outside of the nested query. Inside the brackets/nested query you should just show the top 1

update #summary
  set TopSpeed = CD.TopSpeed, SpeedTime = CD.TimeSent, SpeedDriver = CD.Driver
from
(
  select VehicleId, TimeSent, Driver, Max(TopSpeed), Max(TimeSent)
  from CarData
  GROUP BY VehicleId, TimeSent, Driver 
) as CD

where CD.VehicleId = #summary.VehicleId 
and CD.TimeSent between #summary.Start and #summary.Stop