For my table below, whenever new records are added to the table, I wish to run an update statement that will populate "order_id" for relevant* records with the next incremental value available that has not been used before in the "order_id" in the same table. For example if the max(order_id) is 25, then when running the update it updates the relevant* new record with 26 (i.e. 25 +1 = 26), and then the next relevant record with 27, etc

Relevant records are new records that are added to the table which have "at_home = Y".

In the construct below, I declare @Last_order_id as the starting value (which would be 26 (=25+1)) to use for updates by:

DECLARE @LAST_order_id INT = ( SELECT MAX(order_id) FROM growing_list ) + 1  -- this gives 26

Then in my mind the update would update the first relevant record with 26, however instead it skips one and puts 27.
If I remove the "+ 1" in the declare statement then it works as I want it to but it doesnt make sense given the @last_order_id = 25, and if SET order_id = @LAST_order_id I would expect it to put 25!

drop table growing_list
create table growing_list (order_id int, info varchar(30), at_home varchar(10))

insert into growing_list (order_id , info , at_home )
    select '3' as order_id, 'Peter' as info, 'Y' as at_home union
    select null as order_id, 'Missing_Bob' as info, 'Y' as at_home union
    select '25' as order_id, 'Tall' as info, 'Y' as at_home union
    select '10' as order_id, 'dark' as info, 'Y' as at_home union
    select null as order_id, 'Missing_Jo' as info, 'N' as at_home union
    select null as order_id, 'Missing_Gar' as info, 'Y' as at_home union
    select null as order_id, 'Missing_Gar' as info, 'N' as at_home 

select * from growing_list

SELECT MAX(order_id) FROM growing_list -- 25

--DECLARE @LAST_order_id INT = ( SELECT MAX(order_id) + 1 FROM growing_list )  -- first update should be 26 (25 +1) but gives 27
--DECLARE @LAST_order_id INT = ( SELECT MAX(order_id) FROM growing_list ) + 1 -- first update should be 26 (25 +1) but gives 27
DECLARE @LAST_order_id INT = ( SELECT MAX(order_id) FROM growing_list )  -- first update should be 26 (25 +1) this gives 26 as desired
    
    UPDATE [dbo].[growing_list]
    SET order_id = @LAST_order_id, @LAST_order_id = @LAST_order_id + 1
    WHERE order_id is null
    and at_home = 'Y'

select * from growing_list