Complicated MySql Update Join Query

Question

Have is an example of the problem I'm facing. The database tables are a little different than usual, but needed to be setup this way.

Items: id, order_id, other fields

Items_Drinks: id, drinks, other fields

Orders: id, other fields

Orders_Drinks: id, drinks, other fields

I need to have an update query that will update the Orders_Drinks table with the sum of the Items_Drinks drinks field that have the same order_id as Orders_Drinks id field.

Items: 1 1 ... Items: 2 1 ... Items_Drinks: 1 4 ... Items_Drinks: 2 5 ... Orders: 1 ... Orders_Drinks: 1 9 ...

The Orders_Drinks is currently correct, but if I were to update Items_Drinks with id of 1 to 5, I would need an update command to get Orders_Drinks with id 1 to equal 10.

It would be best if the command would update every record of the Orders_Drinks.

I know my database is not typical, but it is needed for my application. This is because the Drinks table is not needed for all entries. The Drinks table has over 5000 fields in it, so if every record had these details the database would grow and slow for no real reason. Please do not tell me to restructure the database, this is needed.

I am currently using for loops in my C# program to do what I need, but having 1 command would save a ton of time!

Here is my best attempt, but it gives an error of "invalid group function".

update Orders_Drinks join Items on Items.order_id=Orders_Drinks.id join Items_Drinks on Items_Drinks.id=Items.id set Orders_Drinks.drinks=sum(Item_Drinks.drinks);

A table with 5000 fields? Sounds like the schema as a whole needs to be re-thought. I know you said in your question that "it has to be this way", but my guess is that it doesn't have to be that way. Perhaps relational storage is not what you really need, if you in essence need something that is schema-less. — Mike Brant
– Mike Brant, Commented Jun 12, 2014 at 20:05

Rylonian · Accepted Answer · 2014-06-12 21:15:21Z

I think this is what you're wanting.

Edited:

UPDATE `Order_Drinks` a SET a.`drinks` = (SELECT SUM(b.`drinks`) FROM `Items_Drinks` b INNER JOIN `Items` c ON (b.`id` = c.`id`) WHERE a.`id` = c.`order_id`)

That should give you a total of 9 for the Order_Drinks table for the row id of 1.

This is assuming that Orders.id == Orders_Drinks.id and that Items.id == Items_Drinks.id.

This only gets the results of 1 of the item_drinks records, it does not sum them. Must be missing something.

Gordon Linoff · Accepted Answer · 2015-11-15 23:25:56Z

1

You need to do an aggregation. You can do this in the join part of the update statement:

update Orders_Drinks od join (select i.order_id, sum(id.drinks) as sumdrinks from Items i join Items_Drinks id on id.id = i.id ) iid on iid.order_id = od.id set od.drinks = iid.sumdrinks;

edited Nov 15, 2015 at 23:25

answered Jun 12, 2014 at 20:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Nate Over a year ago

This is a much more efficient way of doing this for sure.

Gordon Linoff Over a year ago

@Nate . . . Why do you think this is not particularly efficient? Under many circumstances, its performance will be comparable to the accepted answer.

spencer7593 · Accepted Answer · 2014-06-12 20:31:45Z

Something like this will return the id from the orders_drinks table, along with the current value of the drinks summary field, and a new summary value derived from the related items_drinks tables.

(Absent the name of the foreign key column, I've assumed the foreign key column names are of the pattern: "referenced_table_id" )

SELECT od.id , od.drinks AS old_drinks , IFNULL(td.tot_drinks,0) AS new_drinks FROM orders_drinks od LEFT JOIN ( SELECT di.orders_drinks_id , SUM(di.drinks) AS tot_drinks FROM items_drinks di GROUP BY di.orders_drinks_id ) td ON td.orders_drinks_id = od.id

Once we have SELECT query written that gets the result we want, we can change it into an UPDATE statement. Just replace SELECT ... FROM with the UPDATE keyword, and add a SET clause, to assign/replace the value to the drinks column.

e.g.

UPDATE orders_drinks od LEFT JOIN ( SELECT di.orders_drinks_id , SUM(di.drinks) AS tot_drinks FROM items_drinks di GROUP BY di.orders_drinks_id ) td ON td.orders_drinks_id = od.id SET od.drinks = IFNULL(td.tot_drinks,0)

(NOTE: the IFNULL function is optional. I just used it to substitute a value of zero whenever there are no matching rows in items_drinks found, or whenever the total is NULL.)

This will update all rows (that need to be updated) in the orders_drinks table. A WHERE clause could be added (after the SET clause), if you only wanted to update particular rows in orders_drinks, rather than all rows:

 WHERE od.id = 1

Again, to get to this, first get a SELECT statement working to return the new value to be assigned to the column, along with the key of the table to be updated. Once that is working, convert it into an UPDATE statement, moving the expression that returns the new value down to a SET clause.

Collectives™ on Stack Overflow

Complicated MySql Update Join Query

3 Answers 3

1 Comment

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Related