How can I downgrade a table reference from mysql 8.0* to 5.5*

Question

I use on my mysql 8.0 database a table reference:

update station_temp_data a
   set min_temp = (select min_temp from 
                      ( select min(air_temp) as min_temp
                        from station_temp_data b
                        where b.station_id = a.station_id
                        and DATE_FORMAT(b.mdate, "%Y.%m.%d") = DATE_FORMAT(a.mdate, "%Y.%m.%d")) as Q1) 

This work fine on mysql 8.0 but not on 5.5!

I get on 5.5 the error:

Error : Unknown column 'a.station_id' in 'where clause'

How can I downgrade this SQL to the 5.5 version?

Answer 1

Only for mysql 8.0 at first you must disable ONLY_FULL_GROUP_BY (s. Disable ONLY_FULL_GROUP_BY and What are the benefits of only_full_group_by mode?)

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

The next steps are for both 8.0 and 5.5!

Then create a view with the daily min temperature:

create VIEW min_temp_day as
select 
   min(AIR_TEMPERATURE_C) min_temp, DATE_FORMAT(mdate, "%Y.%m.%d") days, 
   air_temp, station_id 
from station_temp_data
GROUP BY station_id,days

With this view the update is simple, no complicated joining or other handstands:

update station_temp_data a
set min_temp = (select min_temp from min_temp_day b
                where  days = DATE_FORMAT(a.dtg, "%Y.%m.%d") 
                       and b.STATION_ID = a.station_id)

This version is much faster than the in the question!