SQLite UPDATE only one record matching a WHERE clause, with MAX on another filed

Question

Need to modify the data in an SQLite database that has fields similar to:

  hash     (string)
  modTime  (long, time in ms)
  data     (any data, e.g. string)

I know the hash value of the record to set the data for (it's really xxHash of some binary blob). There can be several records with the same hash, but I need to modify only the one with MAX(modTime) value with the matching hash... Can't figure out how to construct the UPDATE command.

Answer 1

You can use NOT EXISTS in the WHERE clause:

UPDATE tablename
SET data = new_data
WHERE hash = ? 
AND NOT EXISTS (SELECT 1 FROM tablename t WHERE t.hash = tablename.hash AND t.modTime > tablename.modTime)

Or use the column rowid to find the row that you want to update:

UPDATE tablename
SET data = new_data
WHERE rowid = (SELECT rowid FROM tablename WHERE hash = ? ORDER BY modTime DESC LIMIT 1)

or with FIRST_VALUE() window function:

UPDATE tablename
SET data = new_data
WHERE rowid = (SELECT DISTINCT FIRST_VALUE(rowid) OVER (ORDER BY modTime DESC) FROM tablename WHERE hash = ?)

Replace new_data with the new value of data and ? with the value of hash you search for.