Update SQL table identify rows to the numbers of rows

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.