inserting an auto inc ID into an existing table

asa_carter

I have a DB table with the fields: location, authority, long, lat. The table has 45000 records. I have inserted an 'id' field at the beginning of the table.

How do I add auto incrementing values startting at 1?

I wrote this but it adds the same value to each record:

<?
include ("connect.php");
$query = mysql_query("SELECT * FROM towns ORDER BY location");
$i=0;
while ($autonum = mysql_fetch_array($query))
{
$i++;
$up_query = mysql_query("UPDATE towns SET id='$i'");
}

cahva

If you had inserted the id as autoincrementing key in the first place, it would automatically add the incrementing id to rows.

Delete the id column and add it again with proper attributes. Eg.:

ALTER TABLE `towns` ADD `id` MEDIUMINT( 7 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

asa_carter

I imported the data from a CSV, I can re-import it if there is a way to add the id field and auto increment at the same time?

Sxooter

Actually, something like:

update table set id=DEFAULT

might work.

I'm a pgsql kinda guy, so I don't know if that works in mysql or not.