Mysql - How To Store Results Of Group By For Use In On Duplicate Key Update

How do I fix the following query? My attempt to alias the result of the SELECT query into table tmp does not work. INSERT INTO vote_counts (list_id, node_id, num_down_votes) (SE

Solution 1:

If I'm not mistaken, this should work:

INSERT INTO vote_counts (list_id, node_id, num_down_votes) 
SELECT vl.list_id, vl.node_id, COUNT(*) AS c
FROM vote_logs vl 
WHERE vl.node_id=2442703AND vl.agree_type='DOWN_VOTE' GROUPBY vl.list_id
ON DUPLICATE KEY UPDATE num_down_votes = vl.c;

If vl.c doesn't work, you may need to use Count(vl.*) since it's an alias -- not sure, would have to test it.

--EDIT

Perhaps using a subquery will resolve this issue:

INSERT INTO vote_counts (list_id, node_id, num_down_votes) 
SELECT list_id, node_id, c
FROM (
    SELECT vl.list_id, vl.node_id, COUNT(*) AS c
    FROM vote_logs vl 
    WHERE vl.node_id=2442703AND vl.agree_type='DOWN_VOTE' GROUPBY vl.list_id
) tempTable
ON DUPLICATE KEY UPDATE num_down_votes = tempTable.c;

Good luck.