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.
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.
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 );
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...
Just surround your query with the IFNULL( QUERY, 0 )
function
http://www.sqlite.org/lang_corefunc.html#ifnull
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With