CODEWITHSUNDEEP
sqlsql-server-2000subquerysql-update
Robert Lancaster
Robert Lancaster

Reputation: 289

Updating multiple rows using a subquery in SQL

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

Upvotes: 3

Views: 3022

Answers (2)

Bert
Bert

Reputation: 82459

Where here's a way to do it, though I don't like it doing the subquery 3 times. It's one of those cases where I hope someone has a better idea.

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

Upvotes: 0

TehBoyan
TehBoyan

Reputation: 6890

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)

Upvotes: 1

Related Questions