Click here to Skip to main content
15,920,217 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to reindex the number after some of the data have deleted. I have see some of the example from internet but not sure why it always show the error message.
ID | Value
1  | One
2  | Two
6  | Three
8  | Four
9  | Five

ID | Value
1  | One
2  | Two
3  | Three
4  | Four
5  | Five

What I have tried:

DECLARE @newid int
SET @newid=0;
UPDATE [dbo].[logs] SET LogId=(@newid:=@newid+1) ORDER BY LogId;

and it show the error message inccorect index near '@newid:'
Updated 22-Sep-19 19:41pm
Patrice T 23-Sep-19 1:01am    
What is the gain of reindexing ?

It depends on how your DB is organised: if ID is an IDENTITY value (and it should be) then you don't change it, and can't unless tell it specifically you want to: tsql - How to update Identity Column in SQL Server? - Stack Overflow[^]

But it's dangerous: ID's are supposed to uniquely identify a row so other tables can cross relate to it, and it you start changing ID values you risk messing up your DB beyond hope of repair.
Instead, create a new RowNumber column, and UPDATE that to the ROW_NUMBER: ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^]
Share this answer
I think := is not allowed, try using the = operator.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900