Geek asks and answers

Sqlite: setting a default value for the maximum subquery if the result is zero

I want to increase the sequence number for the subgroups inside the table, but if the subgroup does not exist, the sequence should start with 1:

For example, in the following, we want the sequence to be set to 1 if there are no entries in the table with class=5; if there is such a record, then the 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 that this does not work for the initial case.

+5
source share
2 answers

In these situations, the function is COALESCE()very convenient:

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

COALESCE, SQL- - MySQL, Postgres ..

+6

More articles:


All Articles