I’m a database noobie and hope this is an easy question…
I want to UPDATE records in my database and track the maximum value in a particular FIELD, not for the whole existing COLUMN.
I can easily do this by first SELECTing the record, and coding a conditional UPDATE if my new value is greater than the current one. But this seems unnecessarily slow since I’m making two calls to the database. (Currently it takes 5 minutes to run my updates)
Is there a way to ask the database (in one call) to update a record if the current value that’s in the field is less than the potential new value?
The examples I’ve found online use the WHEN and CASE operators and I don’t see those listed in the SQlite docs…
you mean something like
update table set column = max(column, newValue) ?
Yes, I think that’s exactly what I was looking for. I’ll give it a try.
I hadn’t found the Max function only the Max column aggregation.
Thanks for the quick reply!
I have a related need to track the “Latest value of a field based on a timestamp of new data” which might occur in non-sequential order.
Update table set column1 = mydata column2 = mytimestamp when mytimestamp > column2
Does SQlite support WHEN?
Mmm my first guess is something like this:
UPDATE Tablename SET Columnname = NewValue WHERE Columnname = (SELECT MAX(Columnname) FROM Tablename)
Case When is what you’re looking for’Can be used in an “expression”
Perfect! I’m going to have to start using a better SQLite tutorial site. The one I’ve been using doesn’t cover all this goodness.