Update SQL table identify rows to the numbers of rows

hamed algazaly 106 Reputation points
2021-09-03T20:48:24.477+00:00

Hello everyone..
If i have sql table with 5 rows and identity ID column..

RowName | ID
Row1 1
Row2 2
Row3 3
Row4. 4
Row5 5

Let say that i delete row4
Then i add Row6 the id Wil be 6
But if i delete Row4 then
Row6 ID will remind 6
And Row5 ID will remind 5..

I want if i delete Row4
Row5 ID will update to ID 4
And Row6 update to ID 5


Help please
Thank you so much

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-09-06T01:57:44.777+00:00

    Hi @hamed algazaly ,

    Welcome to Microsoft Q&A!

    SQL Server does not allow to update the identity column unlike what you could do with other columns with an update statement.

    But use IDENTITY_INSERT which allows explicit values to be inserted into the identity column of a table.

    SET IDENTITY_INSERT YourTable {ON|OFF}

    Please refer below example:

    create table yourtable  
    (RowName varchar(10),  
    ID int identity(1,1))  
      
    insert into yourtable(RowName) values  
    ('Row1'),  
    ('Row2'),  
    ('Row3'),  
    ('Row4'),  
    ('Row5')  
      
    delete from yourtable where id=4  
      
    insert into yourtable(RowName) values  
    ('Row6')  
      
    select * from yourtable  
    order by id  
    

    Output:

    RowName ID  
    Row1 1  
    Row2 2  
    Row3 3  
    Row5 5  
    Row6 6  
    

    I want if i delete Row4
    Row5 ID will update to ID 4
    And Row6 update to ID 5

    If you would like to achieve above, please refer below:

    SET IDENTITY_INSERT yourtable ON  
    GO  
      
    insert into yourtable(id)  
    select 4  
      
    update a   
    set a.RowName=b.RowName  
    from yourtable a  
    left join (  
    select  ROW_NUMBER() over (order by id) rn,rowname   
    from yourtable   
    where rowname is not null) b  
    on a.ID=b.rn  
      
    SET IDENTITY_INSERT yourtable OFF  
      
    select * from yourtable  
    order by id  
    

    Output:

    RowName ID  
    Row1 1  
    Row2 2  
    Row3 3  
    Row5 4  
    Row6 5  
    NULL 6  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 103.5K Reputation points MVP
    2021-09-03T21:19:00.963+00:00

    I can understand that you are relatively new to relational databases. The answer to your question is that you simply do not do those kind of things.

    To start somewhere, ideally, data in a database should be identified with keys in the data itself. For instance, if you have a table of countries, your key will be the country code according to ISO 3166.

    However, sometimes it can be difficult to identify a key that is practically usable, and in such case you can introduce a surrogate key, which typically is a numeric ID (but it can also be a GUID). Such a key you assign once, and never change. Having immutable keys makes your life a lot easier. While these ids are typically sequential, you should consider them as random numbers and not assign any meaning to them.

    If you want a numbering of the rows when you retrieve them, you can get that with the row_number function:

    SELECT RowName, row__number() OVER(ORDER BY RowName)
    FROM   tbl
    

    Finally, beware that the IDENTITY is not primarily an auto-number feature as such. It exists to permit high-concurency inserts, and it is not designed to give consecutive ids. Au contraire, in order to provide high performance, it is designed so that gaps can appear.

    0 comments No comments

  2. Sam of Simple Samples 5,516 Reputation points
    2021-09-03T21:25:21.993+00:00

    If the id is the primary key then you must do deletes and adds, not updates. So if you have:

    ID | RowName
    1 Row1
    2 Row2
    3 Row3
    5 Row5
    6 Row6
    

    Then you need to delete Row5 and add it back with the id of 4 and do the corresponding for subsequent rows. Except if you try to do all of that at once, the previous id 5 would still exist so the best solution depends on the amount of data to process.

    It might be better to maintain a list of deleted ids and to create new records using ids that do not exist.

    0 comments No comments