Need help with sql server query

Question

I have a table in SQL Server 2005 with the following structure:

fname | id
----------
aaa   |
bbb   |
ccc   |

I need a query that will fill the id column with sequential numbers (eg, 1-3 for this example data). I need this to update the table itself, not just a query that shows this in the results.

Answer 1

try this

Update T Set id =  
   (Select Count(*) From Table
    Where fName <= T.fName)
From Table T

Answer 2

For a quick and dirty solution, drop the Id column and recreate it. Adding the Id column back in with something like:

alter table table_name
add Id int identity(1,1)

That will start you out at 1, and increment by 1 I believe. It may or may not work depending on what database system you're using.

Answer 3

UPDATE   MyTable
SET      ID = RowNum
FROM     (SELECT  fname, RowNum = ROW_NUMBER() OVER (ORDER BY fname)
          from    MyTable) B JOIN MyTable A on B.fname = A.fname

Answer 4

If you can accept numbers that are in order but which may not be sequential use an identity field. You may have gaps due to rollbacks and deleted records.

If you don't want gaps, why do you really care if there are gaps? Do you really want to change the ids of every record past it when you delete record 2 and change all the related tables as well? This is usually an extremely bad idea. How had will this affect data integrity and performance? It is usually a poor design choice to have an id field which may change value over time.