Page 1 of 1
SELECT in UPDATE?
Posted: Fri Jul 07, 2006 2:29 am
by someberry
Hi, I need to update a column in a table depending on fields in another table.
For instance:
Code: Select all
------------ ------------
Table: total Table: stock
------------ ------------
id id
total item_1
item_2
item_3
item_4
I need to make a query that will update the `total` column in the `total` table by adding together `item_1`, `item_2`, `item_3`, and `item_4` in the `stock` table.
Does anyone have an idea if this is possible, and if so, how I could go about it?
Thank you,
someberry
Posted: Fri Jul 07, 2006 2:32 am
by feyd
If your server supports subqueries it can be done, a multi-table update may work as well, but I haven't tried them.
MySQL's UPDATE syntax page has details on a multiple table update.
Posted: Fri Jul 07, 2006 3:08 am
by jamiel
Code: Select all
UPDATE total,stock SET total.total = (stock.item_1 + stock.item_2 + stock.item_3 + stock.item_4) WHERE total.id = stock.id
Untested, but give that a go on a test table. Should work.
Posted: Fri Jul 07, 2006 5:43 am
by someberry
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.
Posted: Fri Jul 07, 2006 5:46 am
by Weirdan
try jamiels suggestion
Posted: Fri Jul 07, 2006 5:54 am
by someberry
Luvily jubily. Got it working

Posted: Fri Jul 07, 2006 8:00 am
by Ambush Commander
Note that if something is extremely complicated so that it can't be implemented purely in MySQL, you can always try SELECT ... FOR UPDATE, which will prevent a race condition from being created.