Reindex problem in SQL statement

Question

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.

Before-------------------------
ID | Value
1  | One
2  | Two
6  | Three
8  | Four
9  | Five

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

What I have tried:

SQL

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:'

Posted 22-Sep-19 6:20am

MAN CHUN LIEW

Updated 22-Sep-19 19:41pm

Aarti Meswania

v2

Add a Solution

Comments

Patrice T 23-Sep-19 1:01am

What is the gain of reindexing ?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2019-09-22T06:43:00

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[^]

RickZeeland · Answer 2 · 2019-09-22T06:44:00

Solution 2

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

Posted 22-Sep-19 6:44am

RickZeeland