How to renumber the field value in SQL using SQL script

Question

I have a table, with one Datatype [int] column [LNITMSEQ]. The numbers are all unique. For example,

first row value = 16384,

second row value = 32768 = 16384*2

Before Delete a record,

After Delete Record

May I now how to renumber it using SQL Script? I wish to use Store Procedure to call it

Answer 1

Use ROW_NUMBER to re-number your rows as follows:

DECLARE @SOP10200 table (LNITMSEQ int);

INSERT INTO @SOP10200 (LNITMSEQ)
    VALUES
    (16384),
    (32768),
    (49152),
    (65536);

DELETE FROM @SOP10200 WHERE LNITMSEQ = 16384;

WITH cte AS (
    SELECT LNITMSEQ
        , 16348 * ROW_NUMBER() OVER (ORDER BY LNITMSEQ ASC) AS NEW_LNITMSEQ
    FROM @SOP10200
)
UPDATE cte SET LNITMSEQ = NEW_LNITMSEQ;

SELECT *
FROM @SOP10200
ORDER BY LNITMSEQ;

Returns (ignoring the other columns which aren't relevant):

LNITMSEQ
16348
32696
49044