CODEWITHSUNDEEP
mysqlselectsql-order-bylimit
nuthan
nuthan

Reputation: 465

update+order by in same query mysql

Her are my table structures:

Table1:track_data

| device_id(foreign) | latitude | longitude | activity |

-Stores id, location co-ordinates, activity being the time instant they were inserted.

Table2:user_devices

| device_id(primary) | park_lat | park_long |

Stores id, park_lat/park_long need to be stored with latitude/longitude from track_data table on request(i.e., the latest activity data). Now, how will i store the latest latitude/longitude from Table1 to Table2, in a single query. Is this possible, To mix select and update in same query?

And now my non-functioning intuitive query:)

UPDATE user_devices,track_data
SET user_devices.park_lat = latitude,user_devices.park_long = longitude  
WHERE user_devices.id='33' AND track_data.activity=(SELECT activity FROM track_data 
ORDER BY track_data.activity DESC LIMIT 1)

Upvotes: 2

Views: 1000

Answers (1)

Bohemian
Bohemian

Reputation: 424993

update user_devices 
join (select device_id, latitude, longitude, activity
      from (select device_id, latitude, longitude, activity
          from track_data order by device_id, activity desc) x
      group by device_id) latest_lat_long on latest_lat_long.device_id = user_devices.device_id
set 
park_lat = latest_lat_long.latitude,
park_long = latest_lat_long.longitude;

How this works:

  • The inner-most query orders by device_id then activity latest first
  • The next layer gets just the first record for each device_id (the latest, because we ordered it thus)
  • We then join this to our table so we only need one pass over track_data
  • Finally we update the columns

This query will perform very well, because it isn't doing a query per row like some people would instinctively do.

Upvotes: 1

Related Questions