1

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.

gregko
  • 5,642
  • 9
  • 49
  • 76

1 Answers1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • 1
    Thank you, @forpas, an excellent answer, offering all these options. Particularly since in my naivete, years ago I created these databases using "WITHOUT ROWID" clause. So now I have a choice to either update the databases and include rowid (which may be also useful for other purposes in the future, or use your 1st answer! – gregko Dec 28 '20 at 12:56