0

I'm trying to update one column based on the rounded average of four other columns in the same row.

Here's what the table looks like:

+-----------+----------+--------------+----------+------------+---------------+

| member_id | level_id | friendliness | activity | popularity | participation | +-----------+----------+--------------+----------+------------+---------------+

| 1017895 | 4 | 7 | 5 | 4 | 4 |

+-----------+----------+--------------+----------+------------+---------------+

Here's how I attempted to do this at first:

UPDATE member_levels SET level_id = 
ROUND(AVG(friendliness+activity+popularity+participation)) 
WHERE member_id = '1017895';

The above gave me the error: Invalid use of group function

So, I did a search on Stack Overflow and found this MySQL Error 1093 - Can't specify target table for update in FROM clause

Based on the above, I tried doing a nested subquery like this:

UPDATE member_levels m1 SET m1.level_id = 
 (SELECT * FROM 
    (SELECT ROUND(AVG(friendliness+activity+popularity+participation)) 
     FROM member_levels m2 
     WHERE m2.member_id = m1.member_id)a
  ) 
WHERE m1.member_id = '1017895';

But that gave me the error: Unknown column 'm1.member_id' in 'where clause'

Then I found this question on Stack Overflow: Update column with average calculation

So, based on that question, I tried this:

UPDATE member_levels m1, 
  (SELECT 
   ROUND(AVG(m2.friendliness+m2.activity+m2.popularity+m2.participation))
   rounded_avg 
   FROM member_levels m2 
   WHERE m2.member_id  = m1.member_id
  ) s 
SET m1.level_id = s.rounded_avg 
WHERE m1.member_id = '1017895';

Again, that attempt gave me the error: Unknown column 'm1.member_id' in 'where clause'

What am I missing? How can I update level_id based on the rounded average of friendliness, activity, popularity, and participation?

Thanks in advance!

Community
  • 1
  • 1
Mandiana
  • 157
  • 1
  • 1
  • 9
  • `AVG` doesn't work as you assume. Take a look at the [manual, AVG](http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_avg). It doesn't take your 4 columns, sums their values and divides the result by 4. It calculates the average of all rows ... – VMai Jun 17 '14 at 15:49
  • Not tested: `ROUND(CAST((friendliness + activity + popularity + participation) AS DOUBLE) / 4.0) rounded_avg` should work. – VMai Jun 17 '14 at 15:57

1 Answers1

1

AVG is for row average on a grouping (aggregate).

If you need to manually average multiple columns.. assuming it's a fixed # of columns this would work:

ROUND((friendliness+activity+popularity+participation) / 4)
Janine Rawnsley
  • 1,240
  • 2
  • 10
  • 20