SQL increases the number

Question

Problem:

I want to increase the number based on the table. So, for example, if the table contains a row

1 1 2 3 4 4 4 5 

the mytable column should grow based on this, taking max (row) + 1 in the above column. Therefore, the result should look like this:

 6 6 7 8 9 9 9 10 

This is the code so far:

 OPEN cur DECLARE @WORKING_ON_ID INT FETCH NEXT FROM cur INTO @WORKING_ON_ID WHILE @@FETCH_STATUS = 0 BEGIN SET @MAX_ID = @MAX_ID + 1 UPDATE #WorkingTable SET ID = @MAX_ID WHERE ID = @WORKING_ON_ID FETCH NEXT FROM cur INTO @WORKING_ON_ID END CLOSE cur DEALLOCATE cur 

Could you help me in solving this problem. Thanks!

Answer 1

I think you could do it easily:

 UPDATE your_table SET id = id + (SELECT MAX(id) FROM your_table) 

Answer 2

Wouldn't it be easier to just take the maximum and add it to this identifier column? (Remember: the Identifier column cannot be an identification column , otherwise the update will fail)

 DECLARE @MAXID INT SELECT @MAXID = MAX(ID) FROM #WorkingTable UPDATE #WorkingTable SET ID = ID + @MAXID 

Answer 3

Please use this code:

 Declare @count int = 0 UPDATE table SET @count = code = @count + 1 

Answer 4

Why use a cursor? Won't this solve your problem:

 DECLARE @MAXID int SELECT @MAXID=MAX(ID) FROM YourTable UPDATE YourTable SET ID = ID + @MAXID 

Answer 5

In SQL Server 2005 or later:

 WITH cte AS ( SELECT ID, MAX(ID) OVER () AS delta FROM atable ) UPDATE cte SET ID = ID + delta;