Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 815
Basel Shishani Avatar asked Feb 16 '13 06:02

Basel Shishani


People also ask

Does SQLite support MAX?

The SQLite MAX function is an aggregate function that returns the maximum value of all values in a group. You can use the MAX function to accomplish a lot of things. For example, you can use the MAX function to find the most expensive products, find the biggest item in its group, etc.

How do I add a default value to a column in SQLite?

To set default value for a column in SQLite, use DEFAULT : CREATE TABLE customers ( id INTEGER PRIMARY KEY, store_code TEXT DEFAULT "store1" NOT NULL, name TEXT );


2 Answers

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...

like image 82
mvp Avatar answered Oct 09 '22 19:10

mvp


Just surround your query with the IFNULL( QUERY, 0 ) function

http://www.sqlite.org/lang_corefunc.html#ifnull

like image 43
Najzero Avatar answered Oct 09 '22 19:10

Najzero