How renumber a column after deleting a record

Question

My temporary table has a column holding a sequential numeric "row number".

How can I create a update this "row number" column when a record is deleted from the table?

Example

ID    Name    Row_Number
1     Jack    1
14    John    2
17    Bella   3

If I delete record with ID=14, I'd like record with ID=17 to be updated to be row_number 2

I've tried this:

SET @deletestr = ' DELETE FROM  ' + @tablename
        + ' where isnull(ltrim(rtrim([Row_Number])),0)=isnull(ltrim(rtrim(@originalRow_Number)),0)'

Similar question here: http://stackoverflow.com/questions/230006/renumber-primary-key — Paul Ellery, Jun 07 '16 at 08:04

Paul Ellery · Answer 1 · 2016-06-07T10:14:51.577

0

You could try something like the below, after the record is deleted, to renumber the table.

UPDATE x
SET x.row_number = x.new_row_number
FROM ( 
    SELECT ROW_NUMBER() OVER (Order by Id) AS new_row_number, row_number    
    FROM @tableName
) x

edited Jun 07 '16 at 10:14

answered Jun 07 '16 at 08:14

Paul Ellery

1,615
5
17
31

unfortunately the Row number is done in VB so I can't control it in the SQL Database like this. – Bishoy Ezzat Jun 07 '16 at 08:21
Ok - can you give more detail to question, as it's not currently clear what you need. – Paul Ellery Jun 07 '16 at 10:14

score 0 · Answer 2 · edited Jun 07 '16 at 10:27

0

SET @deletestr = ' DELETE FROM  ' + @tablename
            + ' where isnull(ltrim(rtrim([Row_Number])),0)=isnull(ltrim(rtrim(@originalRow_Number)),0)'

edited Jun 07 '16 at 10:27

JochenJung

7,183
12
64
113

answered Jun 07 '16 at 08:14

kno kno

1
1

score 0 · Answer 3 · answered Jun 07 '16 at 13:30

I recommend you don't bother, because row numbers are easy to derive and a nuisance to maintain.

Any SQL solution to your question will involve a self-join, because that's the way to find the next lowest value. By producing your row number with a Select, you save yourself some work and the server the overhead of updating the column.

How renumber a column after deleting a record

3 Answers3