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