Edit SQL content in increment column

Question

This is my table:

create table empty 
(
    data_id int identity(1,1),
    emp_name varchar(20),
    address varchar(20),
    city varchar(20)
)

This is the data in the table:

data_id   emp_name      address        city 
---------------------------------------------
1         ritesh        210 b_block     sivapur
2         pritma        visvaas khand   lucknow
3         rakesh        nehru nagar     delhi

I want to change value of data_id = 1 to data_id = 10. But how?

Thanks in advance

Answer 1

Some things I want to share with you for your question:

1. You can not change the primary key column value as you gave auto-increment by 1. See the concept of identity: What does (1,1) mean in SQL?

the first 1 means the starting value of column and the second 1 means the increment value of column

2. Do not use SQL Server reserved words like empty for your own database objects (like tables)

3. Not recommended, but still you want to modify, then try this, add new row(s) with the values and delete the row(s) (How to update Identity Column in SQL Server?) )

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

   --create table empty 
   --(
   --    data_id int identity(1,1),
   --    emp_name varchar(20),
   --    address varchar(20),
   --    city varchar(20)
   --)
   
   --select * from empty
   --Insert into empty values
   --('ritesh','210 b_block','sivapur'),
   --('pritma','visvaas khand','lucknow'),
   --('rakesh','nehru nagar','delhi')
   
   select * from empty

   SET IDENTITY_INSERT [empty] ON;

   --give error as we do not change the auto increment column, so comment and then run
   --update [empty] 
   --set data_id = 10 where data_id = 1;

   INSERT INTO [empty] (data_id, emp_name, address, city)
       SELECT 10 data_id, emp_name, address, city 
       FROM [empty]  
       WHERE data_id = 1;

   SELECT * FROM [empty] 

   DELETE [empty] 
   WHERE data_id = 1;

   SET IDENTITY_INSERT [empty] OFF;

   SELECT * FROM [empty] 
 

Answer 2

You probably shouldn't be looking to do this, as your data_id identity column is already guaranteed to have unique values. Manually updating one record to another value might break the contract. That being said, if you must do this, you just need a simple update:

UPDATE empty
SET data_id = 10
WHERE data_id = 1;

Answer 3

You should try to rename the table instead of empty as it shown here