Update Column Serial Wise

There are 2 columns in 1 database for Id and name but the serial number is random.


I want to update those serial number in serial order, how to?



Like

id Name
------------
1 Shouvik
5 Joe
6 Mathew
3 John
4 James
2 Jack


I want to update it like

id Name
------------
1 Shouvik
2 Joe
3 Mathew
4 John
5 James
6 Jack



if i code it like

for x=1 to 4
conndb.execute "update tblcontest set id=" & x 
rs2.movenext
next

Code (markup):

This way its updating all the colum with the number 4 only, any ideas?

 

you don't need it I think, just make ID as a key column in your DB

 

the order of keys in a table doesn't really matter, just use "order by id" when you select or if you must have these numbers you'll have to issue a whole bunch of update statements like:

update tblcontest set id=3 where id = 6
update tblcontest set id=6 where id = 2

 

first of all, if he needs to have the data sorted in the DB, he'd better make it a key column,

second, if you replace 3 with 6, then you might have 2 rows with the id 6 and when you replace it with 2, you might have 3 rows of 2

if he finally decides to update the DB in place of using as a key column, he will have to use a loop