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.