Adding a serial number column in a table (SQL Server 2000)
I have a table in SQL Server 2000, named TABLE_01.
I have a field named ID.
I would like to update it with a serial number starting from 1 (1, 2, 3, 4, ... etc) for all records.
How shoud I do that?
ROW_NUMBER() function does not work.
I have a field named ID.
I would like to update it with a serial number starting from 1 (1, 2, 3, 4, ... etc) for all records.
How shoud I do that?
ROW_NUMBER() function does not work.
if ID is auto-increment you can query to add number column
select TABLE_01.*,
(slect count(*)+1 from TABLE_01 as t where t.ID<TABLE_01.ID) as num
from TABLE_01
order by ID
if not, try remove ID column and run query
ALTER TABLE TABLE_01
ADD ID int identity
ASKER
It doesn't seem to work although I did understand the logic behin it..
It actually brings a new column (or, as you correctly put it, the ID column if I delete it beforehand)
but with '1' only..
2008-10-20-114117.jpg
It actually brings a new column (or, as you correctly put it, the ID column if I delete it beforehand)
but with '1' only..
2008-10-20-114117.jpg
ASKER
Names of fields are not important, I just want to add auto increment in column 'ID'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
then update
update TABLE_01 set ID=NEWID
ASKER
Thank you for your solution, was exactly what I was looking for.
ASKER
I have TABLE_01
ID FIELD1 FIELD2
--------------------------
<NULL> ABC DEF
<NULL> SJH KLH
etc..
and I want ..
ID FIELD1 FIELD2 etc..
--------------------------
1 ABC DEF
2 SJH KLH
etc..