Operand should contain 1 column when joining tables with update

Question

I'm trying to update a table by combining two queries as follows

 UPDATE result, games_played
      SET result.precentage_correct =
(
SELECT user_id, 100*SUM(n_win)/SUM(n_total) AS pct_win FROM
(SELECT user_id, COUNT(user_id) AS n_win,
  NULL AS n_total
  FROM games_played
  WHERE winner != 'n'
  AND game = 1
  GROUP BY user_id
  UNION SELECT user_id, NULL AS n_win,
  COUNT(user_id) AS n_total
  FROM games_played
  WHERE game = 1
  GROUP BY user_id
) AS counts
GROUP BY counts.user_id
)
WHERE result.user_id = games_played.user_id

However I get the error

Operand should contain 1 column(s)

Would anyone know what I'm doing wrong... I can select the result as a new table

SQL fiddle http://sqlfiddle.com/#!2/5374e6/1

Answer 1

Try to add filter criteria in your subquery. The error 'Subquery returns more than 1 row' as you mentioned in comment means the subquery (the one you named AS counts) returned more than one result. Something like this:

UPDATE result, games_played
      SET result.precentage_correct =
(
SELECT 100*SUM(n_win)/SUM(n_total) AS pct_win FROM
(SELECT user_id, COUNT(user_id) AS n_win,
  NULL AS n_total
  FROM games_played
  WHERE winner != 'n'
  AND game = 1
  GROUP BY user_id
  UNION SELECT user_id, NULL AS n_win,
  COUNT(user_id) AS n_total
  FROM games_played
  WHERE game = 1
  GROUP BY user_id
) AS counts
GROUP BY counts.user_id
HAVING counts.user_id = result.user_id
)
WHERE result.user_id = games_played.user_id