Click here to Skip to main content
14,601,062 members
Rate this:
Please Sign up or sign in to vote.
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 ?
Rate this:
Please Sign up or sign in to vote.

Solution 1

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[^]
Rate this:
Please Sign up or sign in to vote.

Solution 2

I think := is not allowed, try using the = operator.

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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100