update a mysql table with another table

[gisele]

I have a mysql table with product number, price, description etc..

A customer gave me an Excel speadsheet with new prices.

I was able to create a new table, and import the contents of the spreadsheet.

So now I have a table with old prices and a table with new prices. Here is an example:
Table 1 contains productid, oldprice
Table 2 contains productid, new price

I have to update the prices in Table 1 with the prices in Table 2
In my PHP script, I'm able to select the fields from multiple tables using the productid as the join, but how to I do the update.

Thanks,

Gisele

[mattw]

I would do something like this:

$query = "SELECT * FROM newtable";
//run query
$row=mysql_fetch_array($result);
while($row) {
$query = "UPDATE oldtable SET price = $row['newprice'] WHERE productid = $row['productid']";
$row=mysql_fetch_array($result);
}

should do the job just fine...

Matt Wade
codewalkers.com

[Anon]

Try to avoid doing loops in code if the same can be achieved in a query - the performance difference is enormous (if nothing else, you're executing one instead of 1 + n queries, where n is number of rows in the table). In this case, this is all you need to do a job:

UPDATE oldtable SET price = nt.newprice FROM newtable nt WHERE oldtable.productid=nt.productid