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
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