Page 1 of 1

updating multiple rows / loop

Posted: Thu Sep 22, 2005 12:57 pm
by imstupid
hello-
So I have a table with 19 rows in it. I wanted to be able to update one field in each row all at once, instead of doing it one at a time. I've attempted to write a basic loop script which

a) takes forever
b) only updates the first row.

The table is set up with an auto_increment ID field set up as the primary key.

here's the code for that hack-job script I tried to write.

Code: Select all

...connect and other stuff here

$getID = mysql_query("SELECT ID FROM tablename"); 

$uniqueID = mysql_fetch_array( $getID )
		 or die(mysql_error());
		 
$ID = $uniqueID['ID'] ;	

while ($ID < 20) {

	$existingname = "SELECT name FROM tablename WHERE ID='$ID'"; 

	$name ="$existingname.0905";

	mysql_query("UPDATE tablename SET name='$name' WHERE ID='$ID'") 
		 or die(mysql_error()); 

 }
 
 echo "name updated";
if anyone out there could point me in the right direction I'd appreciate it.

Posted: Thu Sep 22, 2005 1:19 pm
by Weirdan
Two tips
  • you may reference original value of a field in the UPDATE query:

    Code: Select all

    .... SET name=concat(name, '.0905')
  • UPDATE would affect entire table if you omit the WHERE clause.
thus:

Code: Select all

UPDATE tablename SET name=concat(name, '.0905')