CODEWITHSUNDEEP
sqlite
Tom Ace
Tom Ace

Reputation: 53

Only update column if new value is higher

I want to add a constraint, so a column only updates its value if the new value passed in is greater than its current value. Otherwise it should silently ignore. In pseudocode:

CREATE TABLE t (col INTEGER CHECK (new.value > col.value) ON CONFLICT IGNORE)

SQLite 3.7.4.

Upvotes: 5

Views: 2504

Answers (1)

Tom Ace
Tom Ace

Reputation: 136

I have decided to use MAX():

UPDATE t SET col = MAX(col, newval) [, col2 = xxx, ...]

Although technically it does overwrite the value, at least it can never be lowered.

When using a WHERE clause to enforce this constraint, other columns (e.g. col2) affected by the UPDATE would not be updated if the clause prohibited a match based on col's value.

I could not work out the correct syntax for a trigger to ignore the UPDATE if the constraint was violated. Regardless, I'd imagine using a trigger would incur much greater overhead than a simple MAX() call.

Upvotes: 5

Related Questions