How to update multiple rows based on select statement?

Question

I might be overlooking something very obvious but I cannot get my multiple UPDATE statement to run. What I want to achieve is to SELECT an AVG() value from a table and UPDATE a column based on these values in another table.

I followed the following solution, but could not make it run:

Orginal answer

my solution:

UPDATE
  teams
SET
  teams.overall_rank = q.rank_avg
FROM
  (
    SELECT
      team_id as t_id,
      AVG(rank) as rank_avg
    FROM
      team_list
    WHERE
      rank NOT IN (0, -1)
    GROUP BY
      team_id
    ORDER BY
      rank_avg
  ) q
WHERE
  q.t_id = teams.team_id

The error message:

You have an error in your SQL syntax; it seems the error is around: 'FROM ( SELECT team_id as t_id, AVG(rank) as rank_avg FR' at line 5

I am using MySQL. What am I missing here?

The answer link that you refer to is an `UPDATE` query for **MS-SQL** (you can see the tag). ;) — FanoFN, May 22 '19 at 05:48

score 1 · Accepted Answer · answered May 22 '19 at 05:44

Try below - your syntax is not valid for mysql

UPDATE teams
join
  (
    SELECT
      team_id as t_id,AVG(rank) as rank_avg
      FROM team_list
      WHERE rank NOT IN (0, -1)
      GROUP BY team_id
   ) q on q.t_id = teams.team_id 
SET teams.overall_rank = q.rank_avg

score 1 · Answer 2 · answered May 22 '19 at 05:44

1

I think you need a join:

UPDATE teams t INNER JOIN (
    SELECT
      team_id as t_id,
      AVG(rank) as rank_avg
    FROM team_list
    WHERE rank NOT IN (0, -1)
    GROUP BY team_id
) q ON q.t_id = t.team_id 
SET t.overall_rank = q.rank_avg

The ORDER BY in the subquery is not needed.

answered May 22 '19 at 05:44

forpas

160,666
10
38
76

You were a minute faster than @fa06, so I accepted you answer :) – JoeBe May 22 '19 at 05:57
1

@JoeBe its seems you are wrong becauses fa06 is 36seconds faster than the forpas so your logic is wrong here – Zaynul Abadin Tuhin May 22 '19 at 07:01
2

You are right! I did not hover over the time but it displayed 10min for forpas and 11min for fa06... strange. I adjust it – JoeBe May 22 '19 at 07:09

How to update multiple rows based on select statement?

2 Answers2