mercredi 28 janvier 2015

Sqlite: get max value set to zero when row doesn't exist or is null

I have this table equip_info:



equip_id | fam_id | asset_no
----------|--------|-----------
1 | 1 | 1
2 | 1 | 2


I would like to insert a new equip_id from a different fam_id and to get the asset_no incremented.


If I do so:



INSERT INTO equip_info (fam_id, asset_no)
VALUES (1, (SELECT MAX(asset_no) + 1 FROM equip_info WHERE fam_id = 1))


I get bellow result:



equip_id | fam_id | asset_no
----------|--------|-----------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3


But, if I do so:



INSERT INTO equip_info (fam_id, asset_no)
VALUES (2, (SELECT MAX(asset_no) + 1 FROM equip_info WHERE fam_id = 2))


it said: NOT NULL constraint failed: equip_info.asset_no


If the fam_id = 2 doesn't exist how can I get increment to 1 instead?


I would like to get this result:



equip_id | fam_id | asset_no
----------|--------|-----------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 1


Remark: equip_id is primary key, autoincremented


Aucun commentaire:

Enregistrer un commentaire