Modify rows to increment in MySQL

Question

I have the following table: http://kimag.es/share/59074317.png

columns = [id cid comment]

I need a way to make the values of cid (comment id) increase by 1 for every row in the table.

row 1, cid=0

row 2, cid=1

row 3, cid=2

etc.

Now cid=id because of this php script:

<?php
$con = mysql_connect("localhost","MYUSER","MYPASS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$id=0;
$totalrows=23207; 

mysql_select_db("MYDB", $con);
while($id < $totalrows)
{
$sql = "UPDATE comments SET cid=$id WHERE id=$id";
mysql_query($sql,$con);
$id++;
}

mysql_close($con);
?>

Could someone provide an appropriate mysql query?

Note: I don't have any 'individual keys'... and I need the cid to correspond to a specific comment so I can delete it, modify it, etc. (yes, I should've thought of that before creating the table >_<).

Thanks!

Answer 1

If cid is primary key of your table then you can specify AUTO_INCREMENT attribute to it. Which will automatically assign unique values to all new rows inserted:

-- when use NULL as value for id mysql automatically set next unique number
INSERT INTO table (cid, id, comment) VALUES (NULL, ?, ?);

And you can alter existing data too:

ALTER TABLE table CHANGE cid cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Answer 2

Can't you just do:

ALTER TABLE comments ADD cid INT AUTO_INCREMENT PRIMARY KEY;

You'll need to drop the old cid column first.