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.
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.