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

32 comments:

Anonymous said...

haha~ funny! thank you for your share~ ........................................

Anonymous said...

很喜歡你的blog哦...加油唷 ........................................

Anonymous said...

我們唯一需要恐懼的事,是恐懼本身........................................

Anonymous said...

TAHNKS FOR YOUR SHARING~~~VERY NICE ........................................

Anonymous said...

haha~ funny! thank you for your share~ ........................................

Anonymous said...

很棒的分享~祝福你........................................

Anonymous said...

知識可以傳授,智慧卻不行。每個人必須成為他自己。...............................................................

Anonymous said...

無一事而不學,無一時而不學,無一處而不學。.............................................

麗娟麗娟 said...

Well done!......................................................................

Anonymous said...

i trust everything will be fine. bless you!........................................

于呈均名 said...

我又來看你囉~加油^^ 祝你天天順利開心..............................

Anonymous said...

樂觀進取,勇往直前,持之以恒,是克服困難的妙方。......................................................

俊翔 said...

很用心的blog,推推哦 ..................................................

則義 said...

Never put off till tomorrow what may be done today.................................................................

朱榮 said...

blog不錯唷~我會常常來看的~加油~!!

Anonymous said...

在莫非定律中有項笨蛋定律:「一個組織中的笨蛋,恆大於等於三分之二。」......................................................................

麗珠麗珠麗珠 said...

幸福不是一切,人還有責任。............................................................

Anonymous said...

當一個人內心能容納兩樣相互衝突的東西,這個人便開始變得有價值了。............................................................

Anonymous said...

每次看完你的文章,總是回味許久,要經常發表喔。..................................................................

Anonymous said...

Words are not living in dictionary. Words are living in mind.............................................................

Anonymous said...

成功可招引朋友,挫敗可考驗朋友............................................................

Anonymous said...

希望我的支持可以帶給你快樂--加油.............................................................

Anonymous said...

forgive others but not yourself...................................................

Anonymous said...

多譏樓主分享 正野緊係要回啦............................................................

Anonymous said...

「仁慈」二個字,就能讓冬天三個月都溫暖。.......................................................

Anonymous said...

世間事沒有一樣沒有困難,只要有信心去做,至少可以做出一些成績。..................................................

家唐銘 said...

Of two evils choose the least.............................................................

Anonymous said...

人生中最好的禮物就是屬於自己的一部份............................................................

Anonymous said...

生命是一頓豐富的宴席,有人卻寧可挨餓................................................

文王廷 said...

一個人的價值,應該看他貢獻了什麼,而不是他取得了什麼............................................................

Anonymous said...

Man proposes, God disposes..................................................................

Anonymous said...

生命的意義,是在於活的充實;而不是在於活得長久。............................. ...................................