CODEWITHSUNDEEP
sqlt-sql
Ish
Ish

Reputation: 671

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!

Upvotes: 11

Views: 66140

Answers (5)

Ali Rasoulian
Ali Rasoulian

Reputation: 439

Please Try this Code:

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

Upvotes: 4

Andriy M
Andriy M

Reputation: 77657

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;

Upvotes: 1

Pieter
Pieter

Reputation: 3399

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

Upvotes: 1

Marco
Marco

Reputation: 57573

I think you could do it easily with this:

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

Upvotes: 14

Matten
Matten

Reputation: 17621

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

Upvotes: 4

Related Questions