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:

明宏明宏 said...

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

睿玄 said...

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

RexTemples20144 said...

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

皇雯 said...

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

興偉 said...

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

佩慧娟樺 said...

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

淑君 said...

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

智能 said...

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

麗娟麗娟 said...

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

730A_ngelinaRabideau0 said...

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

于呈均名 said...

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

佩GailBohanan1蓉 said...

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

俊翔 said...

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

則義 said...

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

朱榮 said...

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

王婷珊 said...

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

麗珠麗珠麗珠 said...

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

王名仁 said...

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

與發 said...

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

香昱信張君林 said...

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

吳婷婷 said...

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

賢林賢林 said...

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

楊家堯楊家堯楊家堯 said...

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

RicoLisi0802志竹 said...

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

蕭v柯凡豐珈火 said...

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

仲惠娟惠娟亨 said...

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

家唐銘 said...

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

謝俊林奕宏宏 said...

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

校仪 said...

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

文王廷 said...

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

峻胡邦慧v帆 said...

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

群育航學 said...

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