SELECT in UPDATE?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

SELECT in UPDATE?

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

try jamiels suggestion
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post by someberry »

Luvily jubily. Got it working :)
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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.
Post Reply