sqlite3 - How to UPDATE multiple columns using a correlated subquery in SQLite? -

i want update multiple columns in table using correlated subquery. updating single column straightforward:

update route set temperature = (select amb_temp.temperature                    amb_temp.temperature                    amb_temp.location = route.location) 

however, i'd update several columns of route table. subquery more complex in reality (join nested subquery using spatialite functions), want avoid repeating this:

update route set   temperature = (select amb_temp.temperature                  amb_temp.temperature                  amb_temp.location = route.location),   error = (select amb_temp.error            amb_temp.temperature            amb_temp.location = route.location), 

ideally, sqlite let me this:

update route set (temperature, error) = (select amb_temp.temperature, amb_temp.error                             amb_temp.temperature                             amb_temp.location = route.location) 

alas, not possible. can solved in way?

here's i've been considering far:

• use insert or replace proposed in this answer. seems it's not possible refer route table in subquery.
• prepend update query with clause, don't think useful in case.

for completeness sake, here's actual sql query i'm working on:

update route set (temperature, time_distance) = ( -- (c)   select  -- (b)     temperature.temp,     min(abs(julianday(temperature.date_hrmn)             - julianday(route.date_time))) datetime_dist   temperature     join (       select  -- (a)         *, distance(stations.geometry,route.geometry) distance       stations       exists (         select 1         temperature         stations.usaf = temperature.usaf               , stations.wban_id = temperature.ncdc         limit 1       )       group stations.geometry       order distance       limit 1   ) tmp   on tmp.usaf = temperature.usaf      , tmp.wban_id = temperature.ncdc ) 

high-level description of query:

• using geometry (= longitude & latitude) , date_time route table,
• (a) find weather station (stations table, uniquely identified usaf , ncdc/wban_id columns)
  • closest given longitude/latitude (geometry)
  • for temperatures present in temperature table
• (b) find temperature table row
  • for weather station found above
  • closest in time given timestamp
• (c) store temperature , "time_distance" in route table