MYSQL updating values where max timestamp

Question

I have this query:

select * from weather where ICAO = 'SSPB' and timestamp = (select max(timestamp) from weather where ICAO = 'SSPB');

the result:

timestamp, ICAO, time, wind_angle, wind_speed, gust, visibility, condition, sky, temp, dew, pressure, humidity

2018-04-02 19:20:01, SSPB, 021919, 230, 03, , 9999, , SCT026, 23, 17, 1012, 67%

Now I wanto to update, using the same method as the select

update weather set wind_speed = '10' where ICAO = 'SSPB' and timestamp = (select max(timestamp) from weather where ICAO = 'SSPB');

I get this error:

Error Code: 1093. You can't specify target table 'weather' for update in FROM clause

possible duplicate of https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — cool, Apr 02 '18 at 19:33
Am I correct in assuming that you want to update the same row that was selected? If so, you definitely have a race condition here. Even if you can make the update work, another user may have inserted a new row with a higher timestamp. If they do, you'll modify that row instead. You may want to do your first select as a "select ... for update" (full lock) or if others can continue to read the value "select ... lock in share mode". If you had a primary key on the table, you could then use it in the where clause of the update. — Erik Nedwidek, Apr 02 '18 at 19:43

score 0 · Answer 1 · answered Apr 02 '18 at 19:44

You are a victim of Derived Merge Optimization. However, you can use yet another select row by creating a temporary table and avoid it. Try this :

update weather set wind_speed = '10' where ICAO = 'SSPB' and timestamp =
( select c.cTime from 
      (select max(timestamp) as cTime 
       from weather where ICAO = 'SSPB'
       ) as c
);

score 0 · Answer 2 · answered Apr 02 '18 at 19:47

One possible workaround is to do a JOIN to an inline view (derived table). We can do that with a SELECT statement ...

SELECT t.*
  FROM weather t
  JOIN ( SELECT q.ICAO
              , MAX(q.timestamp) AS max_timestamp
           FROM weather q
          WHERE q.ICAO = 'SSPB'
          GROUP BY q.ICAO
       ) s
    ON t.ICAO      = s.ICAO
   AND t.timestamp = s.max_timestamp 
 WHERE t.ICAO = 'SSPB'

And that same pattern will work for UPDATE. We can convert the SELECT statement into an UPDATE by replacing SELECT ... FROM with UPDATE and adding a SET clause before WHERE clause :

 UPDATE weather t
  JOIN ( SELECT q.ICAO
              , MAX(q.timestamp) AS max_timestamp
           FROM weather q
          WHERE q.ICAO = 'SSPB'
          GROUP BY q.ICAO
       ) s
    ON t.ICAO      = s.ICAO
   AND t.timestamp = s.max_timestamp 
   SET t.wind_speed = '10'
 WHERE t.ICAO = 'SSPB'

MYSQL updating values where max timestamp

Error Code: 1093. You can't specify target table 'weather' for update in FROM clause

2 Answers2