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

I want to update multiple columns in a table using a correlated subquery. Updating a single column is straightforward:

UPDATE route
SET temperature = (SELECT amb_temp.temperature
                   FROM amb_temp.temperature
                   WHERE amb_temp.location = route.location)

However, I'd like to update several columns of the route table. As the subquery is much more complex in reality (JOIN with a nested subquery using SpatiaLite functions), I want to avoid repeating it like this:

UPDATE route
SET
  temperature = (SELECT amb_temp.temperature
                 FROM amb_temp.temperature
                 WHERE amb_temp.location = route.location),
  error = (SELECT amb_temp.error
           FROM amb_temp.temperature
           WHERE amb_temp.location = route.location),

Ideally, SQLite would let me do something like this:

UPDATE route
SET (temperature, error) = (SELECT amb_temp.temperature, amb_temp.error
                            FROM amb_temp.temperature
                            WHERE amb_temp.location = route.location)

Alas, that is not possible. Can this be solved in another way?

Here's what I've been considering so far:

• use INSERT OR REPLACE as proposed in this answer. It seems it's not possible to refer to the route table in the subquery.


• prepend the UPDATE query with a WITH clause, but I don't think that is useful in this case.