Mass update mysql db

[jonathen]

Hi all,

I have a db which I need to update alot of records on. Trouble is, each record has unique values, and when I use the UPDATE command, only the first field is updated. I cannot for the life of me figure out how to mass update the table from the data I have pulled from another table - I am using:

$query1="SELECT * FROM table1 ORDER BY tbl1_id' DESC" or die(mysql_error());
$result1=mysql_query($query1) or die(mysql_error());
$num=mysql_numrows($result1);
mysql_close();  

$i=0;
while ($i < $num) {
$tbl1_id=mysql_result($result1,$i,"tbl1_id");
$field1_tbl1=mysql_result($result1,$i,"field1_tbl1");
$field2_tbl1=mysql_result($result1,$i,"field2_tbl1");
++$i;
}
UPDATE table2 SET field3_tbl2 = '$field2_tbl1' WHERE field1_tbl2 = '$field1_tbl1'" or die(mysql_error());
$result2=mysql_query($query2) or die(mysql_error());
mysql_close();

...then I echo out the newly updated table2 contents, but as I said, only the first row of table2 has field3 updated. I know how to update all rows with one variable if one of the fields contains another set variable, but how do I update all rows with variable data taken from all rows of the other table?

Hope that makes sense. Any help is appreciated.

[Lirpa]

Try :

$query1="SELECT * FROM table1 ORDER BY tbl1_id' DESC" or die(mysql_error());
$result1=mysql_query($query1) or die(mysql_error());
$num=mysql_numrows($result1);

$i=0;
while ($i < $num) {
$tbl1_id=mysql_result($result1,$i,"tbl1_id");
$field1_tbl1=mysql_result($result1,$i,"field1_tbl1");
$field2_tbl1=mysql_result($result1,$i,"field2_tbl1");
UPDATE table2 SET field3_tbl2 = '$field2_tbl1' WHERE field1_tbl2 = '$field1_tbl1'" or die(mysql_error());
$result2=mysql_query($query2) or die(mysql_error());
++$i;
}
mysql_close();

The UPDATE statement must be placed in the while loop. Otherwise it´s only performed once.

Greetings, Lirpa

[jonathen]

Thanks for that, will give it a try...