Thanks you two, I managed to get it working, now it just needs a bit of tweaking.
I need to update each of the `total` table rows according to the stock table row it corresponds to.
e.g.
Code: Select all
------------ ------------
Table: total Table: stock
------------ ------------
id id
stock_id item_1
total item_2
item_3
item_4
Because there might be thousands of rows in the `total` table, I cannot loop through each row updating them as I go along. Because of this I really need to do it all in one query, if it is possible.
This is the SQL I am currently using, however it updates each `table` row, but with the first `stock` row - not its correspnding row:
Code: Select all
mysql_query("UPDATE `total` AS c, (SELECT * FROM `stock` WHERE `id` = c.stock_id) AS s
SET c.total = s.item_1 +
s.item_2 +
s.item_3 +
s.item_4
") or die(mysql_error());
Any help would be greatly appreciated,
someberry.