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!

Dominic Rodger · Accepted Answer · 2009-10-11 20:56:22Z

1

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.

answered Oct 11, 2009 at 20:56

Dominic Rodger

100k37 gold badges204 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ivan Nevostruev · Accepted Answer · 2009-10-11 20:55:52Z

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;

answered Oct 11, 2009 at 20:55

Ivan Nevostruev

28.9k8 gold badges69 silver badges82 bronze badges

Collectives™ on Stack Overflow

Modify rows to increment in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related