SQL increment a number

问题:

Problem:

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

1 1 2 3 4 4 4 5

mytable column should increment based on this taking the max(row) + 1 in the above column. So the outcome 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 please help me in getting a solution to this problem. Thanks!

回答1:

I think you could do it easily with this:

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

回答2:

Wouldn't it be easier to just take the maximum and add it to this ID column? (Remember: the ID column can't be an identity column, otherwise an update will fail)

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

回答3:

Please Try this Code:

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

回答4:

Why use a cursor? Wouldn't this solve your problem as well:

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

回答5:

In SQL Server 2005 or later version:

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