Mysql, how to change the following query to fech each row of a table?

I have an event occurring once 开发者_Python百科a day. I have 2 tables:

• application
• rating

Basically, each application has an avg_score that is given by the average of all the feedbacks given by users that are stored in the table rating in the field score. I wrote an event that once a day refresh this value:

CREATE EVENT MY_DAILY_UPDATE
ON SCHEDULE EVERY 1 DAY STARTS '2011-07-23 23:30:00'
DO

  UPDATE application
     SET `avg_score`= (SELECT AVG(`score`) as new_score
                         FROM `rating`
                        WHERE `ID_APPLICATION` = 1)      
   WHERE `APPLICATION_ID` = 1

It works, but only for the application with ID = 1, cause i wrote it by myself. Instead i need my query to update the field avg_score for each application in the table application.

So i think i need to change the value 1 with a variable ID (ex WHERE APPLICATION_ID = ID_VARIABLE).......and this variable should take the id value of each app in the application table (1,2,3.....4 etc).......but i have no idea about how to change my query.....

Change your sub-query to referrence the values in the outer query. (This makes it a correlated sub-query.)

UPDATE application
SET    avg_score = (
         SELECT AVG(score)
         FROM   rating
         WHERE  ID_APPLICATION = application.APPLICATION_ID
       )

Alternatively, as you're doing this for "all values", just join on the sub-query...

UPDATE
  application
INNER JOIN
(
  SELECT ID_APPLICATION, AVG(score) AS score FROM rating GROUP BY ID_APPLICATION
)
  AS averages
    ON averages.ID_APPLICAITON = application.APPLICATION_ID
SET
  application.avg_score = averages.score

UPDATE application
SET `avg_score`= 
    (SELECT AVG(`score`) as new_score
     FROM `rating`
     WHERE `ID_APPLICATION` = `application.APPLICATION_ID`)