How to increment rows by 1 from a certain range in postgresql

Question

I have a table and multiple rows in it.I want to increment rows of port column by 1 which is above certain range.(this certain range i am getting from another query)

I tried using row_number() over() but first row which has to be incremented is incrementing by row_number instead of 1.

below is my query:

UPDATE criml.model_master mm SET port = row_number + 
(select prev from (SELECT id,name,port,LAG(port) OVER (ORDER BY id )  as prev,port - LAG(port) OVER (ORDER BY id )  as difference  from criml.model_master ) AS foo where difference >9 order by difference asc limit 1) 
FROM ( SELECT id, row_number() over () FROM criml.model_master ORDER BY 1) foo WHERE mm.id = foo.id and port not in (9000,9001) and port > 
(select prev from (SELECT id,name,port,LAG(port) OVER (ORDER BY id )  as prev,port - LAG(port) OVER (ORDER BY id )  as difference  from criml.model_master ) AS foo where difference >9 order by difference asc limit 1) ;

Below is the sample data:

id	port
1	200
2	201
3	670

i want 3rd row to be 202 instead of 201 + 3 (row_number).Ignore the inner query which i am calculating for getting the range.Please assist.

Answer 1

The following query reassigns ports using the same criteria that was in the original post:

WITH base_port AS (
  SELECT port
    FROM (SELECT id, port, lead(port) OVER (ORDER BY port) AS next_port
            FROM model_master) t
           WHERE t.next_port - t.port > 9
           ORDER BY port
           LIMIT 1),
new_ports AS (
  SELECT mm.id, mm.port AS old_port, base_port.port + dense_rank() OVER (ORDER BY mm.port) AS new_port
    FROM base_port CROSS JOIN model_master mm
   WHERE mm.port > base_port.port)
UPDATE model_master mm
   SET port = new_ports.new_port
  FROM new_ports
 WHERE mm.id = new_ports.id
   AND mm.port NOT IN (9001, 9002);

The query preserves the relative order of the ports.

Note: The UPDATE will break if there are enough ports above 9002 to cause new_port to have a value of 9001 or 9002. Additional logic can be added to prevent overlapping values.