mysql update one table with select sum() from another

Question

I have the following query, which works:

select filename, hour, sum(joe) as joe_total, sum(bob) as bob_total
from t1
group by filename, hour

This gives thousands of rows of data under the following columns:

filename, hour, joe_total, bob_total

Each filename contains 24 hours, so the first row of results would be

filename1, 1, [joe_total_value_hour1], [bob_total_value_hour1]

... and the second row would be

filename1, 2, [joe_total_value_hour2], [bob_total_value_hour2]

... etc.

I have another table called t2 with the following fields:

filename, hour, joe_total, bob_total, mary_total, gertrude_total

The table t2 also has thousands of rows (more than the result of the select above), but currently the columns joe_total and bob_total contain only zeros and need to be updated.

So I want to update t2 so that

t2.joe_total = [resuls from the select].joe_total

and similarly for t2.bob_total for each filename/hour.

Any ideas how I could accomplish this?

Danish, no not so far. I had hoped I could just do this from the query window in phpMyAdmin — prokaryote, Aug 31 '15 at 18:17

score 1 · Answer 1 · answered Sep 01 '15 at 14:52

1

Okay, so here's the query that worked:

update t2 t2u
inner join
(SELECT filename, HOUR , SUM( joe) ) AS joe_total, SUM( bob ) AS bob_total FROM t1 GROUP BY filename, HOUR) t
on (t2u.filename =t.filename and t2u.hour = t.hour)
SET      t2u.joe_total = t.joe_total,
    t2u.bob_total = t.bob_total,
    ...

Many thanks to Jon C for helping to break my mental icejam.

answered Sep 01 '15 at 14:52

prokaryote

437
6
16

You're right, my query works on SQL Server and probably other DBMS but not on MySQL. I noticed that your query has an unnecessary closing parenthesis "SUM( joe) )". Glad you worked it out – Jon C Sep 01 '15 at 22:50

score 0 · Answer 2 · answered Aug 31 '15 at 18:30

0

You can try something like this:

UPDATE t2 SET 
    t2.joe_total = t.joe_total,
    t2.bob_total = t.bob_total,
    ...
    FROM  
    (  
        select filename, hour, sum(joe) as joe_total, sum(bob) as bob_total
        from t1
        group by filename, hour 
    ) t  
WHERE t2.filename = t.filename
AND t2.hour = t.hour

answered Aug 31 '15 at 18:30

Jon C

664
4
11

looks like that's zeroing in on a solution, thanks. However, not sure I'm reading it right -- I don't understand what the "FROM" does. – prokaryote Aug 31 '15 at 19:43
You need the "FROM" to include the query on t1 and treat the results as a new table t and then join that table t with the target table t2. – Jon C Aug 31 '15 at 20:16
thanks again. I keep getting a syntax error near 'from (select ... '. Can't understand why, but found this stack thread (http://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables), which points to a solution that involves an inner join. It worked on a single filename/hour combo, so maybe that's the way to go – prokaryote Sep 01 '15 at 13:46

mysql update one table with select sum() from another

2 Answers2