sql - Update mySQL Table with Results of Aggregate Function -

i have aggregate table , detail table holds scores many different games. detail table millions of records , aggregate table has each game , average score different number of days.

example:
aggregate table:

 | id | name  | avg_score_7_days | avg_score_30_days | ----------------------------------------------------- | 1  | game 1| 10.3             | 20.3              | | 2  | game 2| 14.3             | 26.3              | 

detail table:

 | id | name  | date                | score | -------------------------------------------- | 1  | game 1| 2015-07-12 01:00:00 | 20    | | 2  | game 2| 2015-07-12 01:00:00 | 26    | | 3  | game 1| 2015-07-12 01:00:00 | 14    | | 4  | game 2| 2015-07-12 01:00:00 | 9     | 

i use code following update aggregate table nightly via stored procedure:

update `aggregate` aggr inner join (     select game_name, avg(score) avg_score     `detail`     `date` between (curdate() + interval -7 day) , curdate()     group `game_name` ) detail7 on aggr.`game_name` = detail7.`game_name` inner join (     select game_name, avg(score) avg_score     `detail`     `date` between (curdate() + interval -30 day) , curdate()     group `game_name` ) detail30 on aggr.`game_name` = detail30.`game_name` 

the problem have if there no scores of games in 7 days, 30 days, etc subqueries return no records , if 1 of them fails none of columns game updated (due inner join). there way can write query update other columns if result of subquery doesn't return results?

use outer join. can simplify logic, 1 aggregation needed:

update `aggregate` aggr left join         (select game_name,                 avg(case when `date` between (curdate() + interval -7 day) , curdate() score end) avg_score_07,                 avg(case when `date` between (curdate() + interval -30 day) , curdate() score end) avg_score_30          `detail`          `date` between (curdate() + interval -30 day) , curdate()          group `game_name`         ) detail         on aggr.`game_name` = detail.`game_name`     set . . . ;