Update MYSQL Table from multiple rows in another table

Question

I'm trying to update table yy from table xx results by doing sum.

For example (syntax is abstract):

update table_yy
  set sum_of_x_and_y = (
       (select sum(row_x) from table_xx where class_id=1)
                 +
       (select sum(row_y) from table_xx where class_id=1) )

Table xx

row_id   class_id   row_x   row_y
   1        1        4        5
   2        1        5        6
   3        2        6        7
   4        1        7        8

Table yy

class_id   sum_of_x_and_y
   1            35
   2            13

but instead of setting the class_id manually, I would love to do something like inner-join update, but I'm working with 15k+ of records.

Don't practise on the live data. You really need a test database where you can try things out without fear. — MandyShaw
– MandyShaw, Commented Jun 9, 2018 at 8:01

David Cros Piezzoli · Accepted Answer · 2018-06-09 08:38:51Z

2

Here is a query that should do the job

UPDATE table_yy, (
    SELECT class_id, SUM(table_xx.row_x + table_xx.row_y) AS sum_of_x_and_y
    FROM table_xx
    GROUP BY table_xx.class_id 
) AS table_sum
SET table_yy.sum_of_x_and_y = table_sum.sum_of_x_and_y
WHERE table_yy.class_id = table_sum.class_id

answered Jun 9, 2018 at 8:38

David Cros Piezzoli

3051 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user6630782 Over a year ago

Oh wow! you are amazing thank you! the secret all along was "GROUP BY", I just realized that you can do mathematical operations while you group up results!! Thank you , you just taught me something new <3

Gordon Linoff · Accepted Answer · 2018-06-09 11:19:23Z

0

Your approach is fine. You just want a correlated subquery:

update table_yy yy
    set sum_of_x_and_y = (select sum(xx.row_x) + sum(xx.row_y)
                          from table_xx xx
                          where xx.class_id = yy.class_id
                         );

Under many circumstances, this will have better performance, particularly if you have an index on table_xx(class_id).

answered Jun 9, 2018 at 11:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Update MYSQL Table from multiple rows in another table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related