Updating fields in sql incrementing by 1 each time

Question

I am trying to construct an SQL query that would take a set of rows and renumber a field on all rows, starting at where they all match a session ID.

e.g. before change:

SessionID  |  LineNumber  |  LineContents
----------------------------------------------
74666      |  1           | example content
74666      |  2           | some other content
74666      |  3           | another line
74666      |  4           | final line

after change (user has deleted line 2 so the 'LineNumber' values have updated to reflect the new numbering (i.e. line '3' has now become line '2' etc.):

SessionID  |  LineNumber  |  LineContents
----------------------------------------------
74666      |  1           | example content
74666      |  2           | another line
74666      |  3           | final line

So reflecting this in NON proper syntax would be something along these lines

i = 0;
UPDATE tbl_name
SET LineNumber = i++;
WHERE SessionID = 74666;

Searches a lot for this with no luck, any help is great :)

What determines your order? Do you have an ID field, or date field? — GarethD, Oct 28 '13 at 15:38
I guess you mean i-- for each line with LineNumber greater than the deleted record LineNumber. — Francesco De Lisi, Oct 28 '13 at 15:38
IS this your table or do you have any primaryKey or something like that ? — Suraj Singh, Oct 28 '13 at 15:42
In reply to Gareth D - Yes there is a unique LineNumberID field I have left out In reply to jpw - SQL server 2012 In reply to Suraj - yes there is a unique LineNumberID field I have left out — Kelsey Thorpe, Oct 28 '13 at 15:46
Was the SEQUENCE command even an option here? [http://technet.microsoft.com/en-us/library/ff878091.aspx] — Techie Joe, Oct 28 '13 at 16:13

score 2 · Accepted Answer · answered Oct 28 '13 at 15:52

Using Row_Number() function and CTE:

;WITH CTE AS (
  SELECT SessionID, LineNumber, LineContents,
         Row_Number() OVER(PARTITION BY SessionID ORDER BY LineNumber) Rn
  FROM Table1 
)
UPDATE CTE
SET LineNumber = Rn
WHERE SessionID = 74666;

Fiddle Demo

score 1 · Answer 2 · edited May 23 '17 at 12:28

1

You can use ROW_NUMBER ( MS SQL ) or ROWNUM ( Oracle ) or similar inside your UPDATE statement.

Check this

Or this

edited May 23 '17 at 12:28

Community

1
1

answered Oct 28 '13 at 15:39

decho

321
2
10

score 0 · Answer 3 · answered Oct 28 '13 at 15:47

You have 2 main ways to do that.

The first "low level" way is this one (SQL Fiddle here):

DELETE FROM TestTable 
WHERE SESSIONID = 74666 AND LineNumber = 3;

UPDATE TestTable SET LineNumber = LineNumber-1
WHERE SESSIONID = 74666 AND LineNumber > 3

select * from TestTable -- check the result

Here we're assuming you know both LineNumber and SessionID.

The other way is through t-SQL Triggers, a little more complex but it helps you if you don't know info about the rows you're deleting. Give it a try.

score 0 · Answer 4 · answered Oct 28 '13 at 15:57

CREATE  TABLE Trial (
--   ID INT,
 SessionID INT , 
 LineNumber INT ,
 LineContent NVARCHAR(100)

)

INSERT  INTO dbo.trial
VALUES  ( 74666, 1, 'example content' ) ,
        ( 74666, 2, 'some other content' ),
        ( 74666, 4, 'another line' ),
        ( 74666, 5, 'final line' )

You can last deleted LineNumber value and can use that id in your update statement to update rest of the LineNumbers , for instance here Linenumber 3 is deleted so ,

   UPDATE dbo.trial SET LineNumber = LineNumber -1 WHERE LineNumber > 3

score 0 · Answer 5 · answered Oct 28 '13 at 16:09

I would be really inclined to handle this differently if at all possible, and have the Linenumber generated on the fly, to avoid having to maintain a column, e.g.:

CREATE TABLE dbo.T 
(   
    LineNumberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
    SessionID INT NOT NULL,
    LineContents NVARCHAR(MAX) NOT NULL
);
GO
INSERT dbo.T (SessionID, LineContents)
VALUES 
    (74666, 'example content'),
    (74666, 'some other content'),
    (74666, 'another line'),
    (74666, 'final line');
GO
CREATE VIEW dbo.V
AS
    SELECT  LinenumberID,
            SessionID,
            Linenumber = ROW_NUMBER() OVER(PARTITION BY SessionID ORDER BY LinenumberID),
            LineContents
    FROM    dbo.T;
GO

In this your View gives you what you need, and if I delete as follows:

SELECT  *
FROM    dbo.V;

DELETE  dbo.V
WHERE   SessionID = 74666
AND     Linenumber = 3;

SELECT  *
FROM    dbo.V;

You get the output:

LINENUMBERID    SESSIONID   LINENUMBER  LINECONTENTS
1               74666       1           example content
2               74666       2           some other content
3               74666       3           another line
4               74666       4           final line


LINENUMBERID    SESSIONID   LINENUMBER  LINECONTENTS
1               74666       1           example content
2               74666       2           some other content
4               74666       3           final line

Example on SQL Fiddle

So you maintain your sequential linenumber field without actually having to update a field. This of course only works if you can rely on a field (such as CreatedDate, or an ID column) to order by. Otherwise you will have to maintain it using triggers, and update statements as suggested in other answers.

Updating fields in sql incrementing by 1 each time

5 Answers5