How to: assign IDs/SortVal values using ROW_NUMBER()

I have a lookup table L_MyLookup with columns ID and NAME.
Later, my client wanted a SORTVAL column because they want to define the order of the values through that column instead of ID.

So
  1. add the SORTVAL column
  2. update the values
  3. alter SORTVAL to become not nullable
Like the following code:


ALTER TABLE L_MyLookup

ADD SORTVAL int NULL

go

UPDATE L_MyLookup set SORTVAL = temp.SORTVAL

FROM

(SELECT ID, ROW_NUMBER() over (order by ID) as SORTVAL

FROM L_MyLookup) as temp

WHERE temp.ID = L_MyLookup.ID

go

ALTER TABLE L_MyLookup

ALTER COLUMN SORTVAL int NOT NULL

No comments: