UPDATE query SUM multiple selects

Question

I need to update a field calculated by the sum of multiple selects. The selection part is working, but I can't find a way to update the user table

user

+------+---------+
| id   | total   |
+------+---------+
| 1    |         | 
| 2    |         |

unita

+------+-------+-----+
| id   | uid   | num |
+------+-------+-----+
| 1    | 1     | 25  |
| 1    | 2     | 10  |

unitb

+------+-------+-----+
| id   | uid   | num |
+------+-------+-----+
| 9    | 1     | 225 |
| 9    | 2     | 10  |

class 

+------+--------+------+
| id   | name   | cost |
+------+--------+------+
| 1    | class1 | 100  |
| 9    | class9 | 500  |

SELECT uid, SUM(score) FROM (
    SELECT unita.uid, SUM(class.cost * unita.num) AS cost FROM unita, class WHERE unita.id = class.id GROUP BY unita.uid
    UNION
    SELECT unita.uid, SUM(class.cost * unitb.num) AS cost FROM unitb, class WHERE unitb.id = class.id GROUP BY unitb.uid
) x GROUP BY uid

The update command should sum all cost per user

User 1: (25*100)+(225*500) = 115000
User 2: (10*100)+(10*500) = 6000

It this possible within 1 SQL command. The unit tables are locked, so I can't modify anything

Just add `update user u join ( your select query till group by uid )y on y.uid = u.uid set u.total = y.score` also give alias for the select part as `SELECT uid, SUM(score) as score ...` — Abhik Chakraborty, May 08 '15 at 11:01
http://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables — Mudassir Hasan, May 08 '15 at 11:02
See this link below [link][1] hope it help you. [1]: http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query — Chanom First, May 08 '15 at 11:03

score 1 · Accepted Answer · answered May 08 '15 at 11:07

You can use join to bring in the results from your subquery:

UPDATE user u JOIN
       (SELECT uid, SUM(score) as total
        FROM (SELECT unita.uid, SUM(class.cost * unita.num) AS cost
              FROM unita JOIN
                   class 
                   ON unita.id = class.id
              GROUP BY unita.uid
              UNION ALL
              SELECT unita.uid, SUM(class.cost * unitb.num) AS cost
              FROM unitb JOIN
                   class 
                   ON unitb.id = class.id
              GROUP BY unitb.uid
             ) x
        GROUP BY uid
       ) newvals
       ON u.id = newvals.uid
    SET u.total = newvals.total;

Three notes:

Note the use of UNION ALL instead of UNION. Not only does this improve performance because duplicates are not eliminated, but it also fixes a potential problem if both subqueries return the same value.
Note the use of proper join syntax. Simple rule: never use commas in the from clause.
This will not set the total to 0 if there is no match. If you desire this, change the join to a left join and the set to SET u.total = COALESCE(newvals.total, 0).

Thanks for your help. I never knew this was possible. – EspieNL May 08 '15 at 12:50 — EspieNL, May 08 '15 at 12:50

score 0 · Answer 2 · answered May 08 '15 at 11:05

You can use the update-join syntax:

UPDATE `user` u
JOIN   (SELECT   uid, SUM(score) AS total
        FROM     (SELECT unita.uid, SUM(class.cost * unita.num) AS cost 
                  FROM     unita, class 
                  WHERE    unita.id = class.id 
                  GROUP BY unita.uid
                  UNION ALL
                  SELECT   unitb.uid, SUM(class.cost * unitb.num) AS cost
                  FROM     unitb, class 
                  WHERE    unitb.id = class.id
                  GROUP BY unitb.uid) x 
        GROUP BY uid) s ON s.uid = u.id
SET    u.total = s.total

Notes:

The inner query in the OP has a bug. Since it uses union instead of union all, if the same uid has the same total score in both units, it will only be counted once, instead of twice. The above query fixes this.
Implicit joins have been deprecated for ages. The above query still uses them to math the OP's style, but the use of explicit joins is highly recommended.

E.g.:

UPDATE `user` u
JOIN   (SELECT   uid, SUM(score) AS total
        FROM     (SELECT unita.uid, SUM(class.cost * unita.num) AS cost 
                  FROM     unita
                  JOIN     class ON unita.id = class.id 
                  GROUP BY unita.uid
                  UNION ALL
                  SELECT   unitb.uid, SUM(class.cost * unitb.num) AS cost
                  FROM     unitb
                  JOIN     class ON unitb.id = class.id
                  GROUP BY unitb.uid) x 
        GROUP BY uid) s ON s.uid = u.id
SET    u.total = s.total

Are all those little GROUP BYs more efficient than one big one? — Strawberry, May 08 '15 at 11:54
Hi Mureinik, thanks for your help. I marked Gordons answer as solution. His answer was a little more complete. — EspieNL, May 08 '15 at 12:51

UPDATE query SUM multiple selects

2 Answers2