Sqlite: Setting default value for a max sub-query if result is null

default, max, sql, sqlite, subquery

Solution

In these situations, function `COALESCE()` comes very handy:

UPDATE order
SET class = 5,
    sequence = coalesce(
        (SELECT max(sequence)
         FROM order 
         WHERE class=5),
        0
    ) + 1
WHERE order_id = 104

Another good thing about `COALESCE` that it is supported by most other SQL engines - MySQL, Postgres, etc...

Problem

I want to increment a sequence number for subgroups within a table, but if the subgroup does not exist then the sequence should start with 1: For example, in the following, we want sequence to be set to 1 if there exists no records in the table with `class=5`; if there exists such records, then sequence should take the value max sequence (in the subgroup `class=5`) + 1: ``` update order set class=5, sequence=(select max(sequence) from order where class=5)+1 where order_id=104; ``` The problem is the above doesn't work for the initial case.

Original source