CODEWITHSUNDEEP
sql-server
akd
akd

Reputation: 6758

How to Incremental ID column with UPDATE from specific value in SQL

I have a table as below

ID Name Code
1   x     y
5   c     v
3   v     h
10  k     4
23  m     g

Now I would like to re order number from 5000 as below

ID Name Code
5001   x     y
5002   c     v
5003   v     h
5004   k     4
5005   m     g

what would be the quickest query SQL Server to handle this as the table contains over million rows?

Upvotes: 0

Views: 60

Answers (4)

empric
empric

Reputation: 30

execute this, will get your desired result.

SET @i = 5000;
UPDATE table_name SET ID=(@i:=@i+1);

Upvotes: 0

Dudi Konfino
Dudi Konfino

Reputation: 1136

use cursor to update all rows in the table

 DECLARE @i int=5000
DECLARE cur cursor FAST_FORWARD READ_ONLY
FOR
 SELECT  id
FROM tbl
DECLARE @d varchar(50)
OPEN cur
 FETCH NEXT FROM cur INTO @d
 WHILE @@FETCH_STATUS=0
 BEGIN 
UPDATE dbo.tbl
SET id=@i+1
WHERE id=@d

FETCH NEXT FROM cur  INTO @d
END
Close  cur 
DEALLOCATE cur
SELECT * FROM tbl

Upvotes: 0

Pawan
Pawan

Reputation: 1075

Try this solution 

declare @a as int;
SET @a = 5000;  
    UPDATE tablename SET @a=id=@a+1;

Upvotes: 1

Saravana Kumar
Saravana Kumar

Reputation: 3729

Use this query.

UPDATE A SET A.ID = 5000 + B.RowNUM 
FROM TempTable AS A 
INNER JOIN 
(SELECT *, ROW_NUMBER() OVER(order by ID) AS RowNUM FROM TEMPTABLE) AS B 
ON A.ID = B.ID

Upvotes: 0

Related Questions