CODEWITHSUNDEEP
mysql
Justinas R.
Justinas R.

Reputation: 301

Insert new column, increment all rows

Is this possible using only mysql:

Insert a new column and example - "pos". Every row in the table has to have unique,incresing value of "pos" like - 1,2,3,4,5.

In php this would be an easy job:

$query = "SELECT * FROM example"; 

$result = mysql_query($query) or die(mysql_error());

$counter = 0;
while($row = mysql_fetch_array($result)){
    mysql_query( "UPDATE example SET pos = ".++$counter." WHERE id = ".$row['id']." );
}

Upvotes: 0

Views: 72

Answers (3)

Bitmap
Bitmap

Reputation: 12538

As already suggested, the most efficient solution is to use auto incremental on pos.

Alternatively if your use case do not permit you, then try similar:

"UPDATE example SET pos = ((select max(pos) from example) +1) WHERE id
= ".$row['id']."

Upvotes: 1

Alfie
Alfie

Reputation: 2350

Give your pos mysql field the auto_increment attribute - this will index it, make it unique, and increment by 1 on each insert

Upvotes: 0

echo_Me
echo_Me

Reputation: 37243

yes you can do it like that

    UPDATE example SET pos = pos+1  WHERE id = ".$row['id']."

Upvotes: 1

Related Questions