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