CODEWITHSUNDEEP
mysqlsql-update
prokaryote
prokaryote

Reputation: 437

mysql update one table with select sum() from another

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?

Upvotes: 0

Views: 1557

Answers (2)

prokaryote
prokaryote

Reputation: 437

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.

Upvotes: 1

Jon C
Jon C

Reputation: 664

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

Upvotes: 0

Related Questions