插入到表中，当与相同数据的其他行进行比较时，列增加1

I will essentially have a table with data like

id, parent_id, child number
1, 5, 1
2, 5, 2
3, 5, 3
4, 5, 4
5, 6, 1
6, 6, 2

What I need is an SQL Query that would be able to add a row representing a new Child of parent id 5, so would be told something along the lines of

INSERT INTO `table` (`parent_id`)
VALUES ('5')
SET `child_number` = 1 + MAX(`child_number`) WHERE `parent_id` = '5'

But i doubt that that sql would work. Any help would be greatly appreciated, thanks.

You can use an insert query like this:

INSERT INTO TABLE (PARENT_ID, CHILD_NUMBER)
VALUES
('5', (SELECT MAX(CHILD_NUMBER)+1 FROM TABLE WHERE PARENT_ID= '5') );

You could do something like this:

INSERT INTO `table` SET 
    `parent_id` = '5',
    `child_number` = (SELECT MAX(`child_number`) FROM `table` WHERE `parent_id` = '5') + 1