Update MAX value in a RECORD

Databases
1

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…

Suggestions???

Thanks, Joe

2

you mean something like
update table set column = max(column, newValue) ?

3

Norm

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?

Cheers, Joe

4

Mmm my first guess is something like this:

UPDATE Tablename SET Columnname = NewValue WHERE Columnname = (SELECT MAX(Columnname) FROM Tablename)

5

http://sqlite.org/lang_expr.html

Case When is what you’re looking for’Can be used in an “expression”

6

Norman

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.

Cheers, Joe