-2

Basically I need to run this query in SQL Server. I executed in MySQL and it works fine.

SET @startID:=29;
update test
set ID=@startID:=@startID+1;

Thanks

LIB
  • 21
  • 1
  • 7
  • What is this query supposed to do? Just set a field to 30? Have you *tried* something, or even checked the syntax for variables? – Panagiotis Kanavos Feb 09 '18 at 08:15
  • What he is trying to do is update the ID column with the value of the variable @startID and make it increment everytime it updates a row. – MBijen Feb 09 '18 at 08:19
  • If you want to create incrementing values, the MySQL query is actually a hack that can easily fail. SQL Server has sequences, IDENTITY columns and ranking functions. – Panagiotis Kanavos Feb 09 '18 at 08:20
  • 1
    @MBijen that's the attempted hack, not what the OP wants to achieve. Why not increment the value before the UPDATE for example? Why not set it to 30 from the start? – Panagiotis Kanavos Feb 09 '18 at 08:20
  • @panagiotis-kanavos I agree with you. – MBijen Feb 09 '18 at 08:22

2 Answers2

0

try this format 

declare @startid int;
set @startid = 29;
update test set ID = @startid+1;
Ajay
  • 764
  • 4
  • 12
0

You can do it as followed:

SET @startID = 29
update test
set user_id = @startID, @startID=@startID+1

It will increment the @startID variable everytime it updates.

MBijen
  • 317
  • 1
  • 13
  • Why do this at all instead of eg incrementing the variable *before* the update? And is the OP's intention to set all IDs to the same number? There are many ways to create number sequences in SQL Server that don't require quirky updates – Panagiotis Kanavos Feb 09 '18 at 08:27
  • The query provided created a number sequences in mysql. I agree on you that there are better ways to do this in SQL Server. – MBijen Feb 09 '18 at 08:31