CODEWITHSUNDEEP
sqlsql-servert-sql
Sam
Sam

Reputation: 325

Increment each field in a column by 1 - SQL Server 2008

I need to increment each field in a column by 1 starting from 2866. I've built the following query, but all it does is set each field to 2867 as opposed to 2868, 2869 etc...

DECLARE @a int
SET @a = 2866
UPDATE view_kantech_matched SET image_id = @a + 1
WHERE image_id = null

Any suggestions? Thanks

Upvotes: 0

Views: 63

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460048

If you want to update it to a value that starts at 2866 and increaments with every row you have to specify the column(s) to be used for the ordering.

Then you can use ROW_NUMBER:

WITH NoImageIDS AS
(
    SELECT vkm.*, RN = ROW_NUMBER() OVER (ORDER BY AnyColumn ASC)
    FROM dbo.view_kantech_matched vkm
    WHERE image_id IS NULL
)
UPDATE NoImageIDS SET image_id = RN + 2866

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

The general structure for doing this would be:

;WITH Numbered as (
    SELECT  *,ROW_NUMBER() OVER (ORDER BY <some column>) rn
    FROM view_kantech_matched
    WHERE image_id is NULL
)
UPDATE Numbered SET image_id = 2865 + rn

But I don't know what <some column> would be.

Upvotes: 4

TheMadDBA
TheMadDBA

Reputation: 436

DECLARE @a int
SET @a = 2866
UPDATE view_kantech_matched SET image_id = @a, @a=@a + 1
WHERE image_id = null

Upvotes: 3

Related Questions