snippetsqlModerate

How to update Column related to Row Number

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

numberupdatecolumnrelatedhowrow

Problem

Is there any easy way to update Column by Row number not a PK
ex:

UPDATE contact m
SET    ContactNumber = sub.rn + 500
FROM  (SELECT Id, row_number() OVER (ORDER BY Id) AS rn FROM contact) sub
WHERE  m.Id = sub.Id;

Solution

UPDATE contact
SET ContactNumber = (@rownum := 1 + @rownum) + 500
WHERE 0 = (@rownum:=0)
-- for a given client, for example, Client_Id=123, use
-- WHERE Client_Id = 123 + (@rownum:=0)
ORDER BY id;

Code Snippets

UPDATE contact
SET ContactNumber = (@rownum := 1 + @rownum) + 500
WHERE 0 = (@rownum:=0)
-- for a given client, for example, Client_Id=123, use
-- WHERE Client_Id = 123 + (@rownum:=0)
ORDER BY id;

Context

StackExchange Database Administrators Q#228650, answer score: 14

Revisions (0)

No revisions yet.