Iterating through MySQL update

Anon

I have a table (table_D) populated with identical integers (num_to_change) for one field (dlr_id) and need to UPDATE them all to become unique numeric identifiers for each individual row. (The table's id field is already set to auto-incremented for each of these rows).

I'm floundering trying to iterate or increment a query like "UPDATE table_D SET dlr_id = (some incremented value) WHERE dlr_id = (num_to_change)";

I realize this sounds relatively simple, but for some reason I'm stalled. I've tried wrapping it in 'while', 'for' and 'if' statements but succeed only in updating ALL the (num_to_change) to a single value...which just replaces all the records with another identical integer.

The PHP is no sweat...it's getting the unigue integers into the MySQL db that is causing me headaches.

Any suggestions appreciated.

Anon

If this is a one-time operation, why not update the field to contain the unique value found in your auto_increment field?

UPDATE table SET num_to_change=auto_increment_field;

Anon

Thanks for the response.
The only problem I'd have with something like:

"UPDATE table_D SET dlr_id = ($dlr_id + $id) WHERE dlr_id = '$num_to_change'"

is that it would increment all the dlr_id integers by only one (just like the DB did when it auto-incremented the id field on original row entry).

I was hoping to issue a truly unique dlr_id to each of the rows.

Basically, I'm trying to make the MySQL UPDATE query loop through a function each time it finds the "num_to_change" in a row.

Anon

Can you add another column using the ALTER command, then use a loop to set the column to 1,2,3,4...etc. Then erase the old id column and rename the new one to the old ones name. That'd be the easiest way.

Is the column the same number for the entire table? i.e. every row is equal to 2. or are only some rows duplicates?

1
2
3
3
3
4
5
6
6
6
6
7

Something like that?

---John Holmes...

Anon

Yes, I have several hundred entries unique, but a few hundred that are identical as you suspected...so I'm trying to UPDATE just those. I'll try the ALTER table method you suggest.

Here's what I've been attempting so far:

//MySQLQuery is already set up to deliver the host, db, pwd
//$id_to_change is a posted var coming in from an admin form
//get_unique_id() just calls a function that
//creates a random six digit integer

<?
php_track vars;

$result = MySQLQuery("SELECT dealer_id, id FROM dealers WHERE dealer_id = '$id_to_change'",
"There was an error executing change_id.php",
"Sorry, change_id.php update query failed.");

while ($row = mysql_fetch_array($result)){
$dealer_id = $row['dealer_id'];
}//end while

$count = mysql_num_rows($result);

for ( $i = 0; $i < $count; $i++) {
$new_dealer_id[$i] = get_unique_id();
MySQLQuery("UPDATE dealers SET dealer_id='$new_dealer_id[$i]' WHERE dealer_id='$id_to_change'",
"There was an error executing change_id.php",
"Sorry, change_id.php update query failed.");
}//end for

mysql_free_results;
?>