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

Question

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.

For completeness sake, here's the 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))) AS datetime_dist
  FROM temperature
    JOIN (
      SELECT  -- (A)
        *, Distance(stations.geometry,route.geometry) AS distance
      FROM stations
      WHERE EXISTS (
        SELECT 1
        FROM temperature
        WHERE stations.USAF = temperature.USAF
              AND stations.WBAN_ID = temperature.NCDC
        LIMIT 1
      )
      GROUP BY stations.geometry
      ORDER BY distance
      LIMIT 1
  ) tmp
  ON tmp.USAF = temperature.USAF
     AND tmp.WBAN_ID = temperature.NCDC
)

High-level description of this query:

using geometry (= longitude & latitude) and date_time from the route table,
(A) find the weather station (stations table, uniquely identified by the USAF and NCDC/WBAN_ID columns)
- closest to the given longitude/latitude (geometry)
- for which temperatures are present in the temperature table
(B) find the temperature table row
- for the weather station found above
- closest in time to the given timestamp
(C) store the temperature and "time_distance" in the route table

You could put the subquery without the correlation into the WITH, and then use simple correlated subqueries to select from that. — CL.
– CL., Commented Apr 1, 2015 at 11:33
Is that possible with the actual SQL query I added to the question? Will that not be much slower? — Brecht Machiels
– Brecht Machiels, Commented Apr 1, 2015 at 12:38

jhnc · Accepted Answer · 2024-02-27 04:52:05Z

0

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

?

answered Feb 27, 2024 at 4:52

jhnc

18.7k2 gold badges14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related