CODEWITHSUNDEEP
mysql
Mandiana
Mandiana

Reputation: 157

MYSQL Update Column Based on Rounded Average of Other Columns in Row

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!

Upvotes: 0

Views: 601

Answers (1)

Janine Rawnsley
Janine Rawnsley

Reputation: 1258

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)

Upvotes: 1

Related Questions