Programmatically updating w. SQL

Question

We currently have a system setup that gives each type of work a 3-letter "code". I was able to wrestle a little bit with management about moving them to an int value so I can autopopulate and run with a uniqueidentifier. So they're on board with that now.

Current table:

tblWorkID
WorkID: [3 digit keys]

What I would like/need is to Update all the WorkID keys into an int value, ascending. This is what I have so far:

SELECT distinct WorkID
from tblWorkID
order by WorkID

Any idea how I can put the logic in there to create a running number based off that data or am I trying to do too much with the SQL? It's 482 total entries and I'd rather not do that by hand (but will if needbe.)

Like this: (Data shown)

AAA Apple Picking
AAB Construction
AAC General Cleaning

And have it populate to something like this:

1 Apple Picking
2 Construction
3 General Cleaning

(Disregard the text next to the numbers and letter sequences, I put it in there for explanation.)

Update: What I currently have is WorkID: [3-letter sequence] is just another field added into the WorkID table (WorkHrs, WorkContact, WorkContract, etc.) So there are multiple WorkID's of say, AAA and AAC within the table--the data integrity is very very well, cloudy.

Update2: My current table structure is like this (I'll be adding in another ID uniqueidentifier field soon):

tblWorkID
WorkId
WorkHrs
WorkContact
WorkContractID
WorkMgrID
WorkDetails

Answer 1

EDIT: Based on your comment below (and some assumptions...) I think you can use DENSE_RANK instead of row_number:

 SELECT DENSE_RANK () OVER (ORDER BY <your 3 letter code>,

That way, all instances of the same three letter code will get the same number.

If I understand you, you can use the ROW_NUMBER() function to generate your running number:

SELECT ROW_NUMBER() OVER (ORDER BY <your 3 letter code>,
...

Or, depending on your current table structure, you may be able to just add an identity column.

ALTER TABLE <Your Table>
ADD <NEW COLUMN NAME> Identity(1, 1)

Answer 2

Create an ID per WorkID

create table T (id int identity(1,1), WorkID char(3))
    insert T select distinct WorkID from thetable order by WorkID asc

Update the existing

update thetable set WorkID = (select id from T where T.WorkID = thetable.WorkID)

Change the column type to INT.