MYSQL Update data based on top 10 records of each user I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of user_score in user_scores table. I have tried following two queries but not successful with these.

1.

UPDATE      user
SET         user.user_score = (
    SELECT AVG(score) as avg_score FROM (
        SELECT score FROM user_scores WHERE user_id = user.id ORDER BY id DESC LIMIT 0,10)as x)


I am facing error Unknown column 'user.id' in 'where clause' cuz I am not able to pass variable to subquery of the query 


Other thing I have tried by JOIN but in JOIN I am not able to calculate AVG(score) of each user based on latest 10 records.


Please suggest.
I may have a solution, but first please see if this computes the avg_scores correctly:

SELECT
        user_id, AVG(score) AS avg_score
    FROM
      ( SELECT  @prev := 0, @n := 0 ) init
    JOIN
      ( SELECT  @n := if(user_id != @prev, 1, @n + 1) AS n,
                @prev := user_id,
                user_id, score, id
            FROM  user_scores
            ORDER BY
                user_id  DESC,
                id  DESC
      ) x
    WHERE  n &lt;= 10
    GROUP BY user_id;


Recommend INDEX(user_id, id, score) on user_scores .

If that works, then a multi-table UPDATE may finish the task:

UPDATE   user
    JOIN ( the above ) AS y  USING(user_id)
    SET  user.user_score = y.avg_score;