[RESOLVED] Update a table, row by row?

facelikebambi

Apologies for serious newbie question! I'm an old Foxpro coder just getting to grips with php/mqsql...

So I have a simple table with 2 fields: ID (int) and NAME (varchar)

I'd like to loop through the table and update ID with an incrementing number each time.

I know how to get the number of rows using mysqli_num_rows, but not sure how to write the update statement. Can I do something like..?

for ($i=1;$i<=$rowcount;$i++)
{
$sql = "UPDATE mytable SET id=$i WHERE [I]row_number[/I]=$i";
$result = mysqli_query($cxn,$sql);
}

Not too worried about optimisation, as code only needs to be run once.

Thanks!

djjjozsi

id fields usually primary and autoincrement type, that will do automatically what you're doing.

if it is a live system, ID field should not changed. if its a related table, the foreign keys from this table will messed.

NogDog

There is no such thing as a "row number" as far as MySQL is concerned. (Well, internally it may have some such concept, but there is nothing in terms of SQL that lets you specify a table row by an arbitrary row number.)

You could create a 2nd table with an auto-incrment primary key field, then populate it something like:

INSERT INTO `new_table` (`name`) SELECT `name` FROM `old_table`

Then you could delete the records from the old table and repopulate it from the newly created version above (after redefining the old table to have its ID field be an auto-incrmented primary key, so that you don't have to worry about this again).

facelikebambi

Ah yes - that's really helpful. Thanks!