MySQL: INSERT INTO SELECT issues Consider the following tables:

=&gt; table1
| aid |
| --- |
| a1  |
| a1  |
| a3  |
| a2  |
| ... |

=&gt; table2
| aid | count | va    | vb    |
| --- | ----- | ----- | ----- |
| a1  | c1    | va1   | vb1   | 
| a2  | c2    | va2   | vb2   |
| ... | ...   | ...   | ...   |


Basically, table2 aggregates data from several other tables, including from table1.
One way to update table2 data in MySQL is:

INSERT INTO `table2`(`aid`, `count`)
SELECT `src`.`aid`, `src`.`count` FROM
(
    SELECT `aid`, COUNT(`aid`) AS `count`
    FROM `table1`
    GROUP BY `aid`
) AS `src`
ON DUPLICATE KEY UPDATE `table2`.`count` = `src`.`count`


Although this code works, it seems to be wordy (going around in circle).
At first, a better approach would be:

INSERT INTO `table2`(`aid`, `count`)
SELECT `aid`, COUNT(`aid`) AS `count`
FROM `table1`
GROUP BY `aid` AS `src`
ON DUPLICATE KEY UPDATE `table2`.`count` = `src`.`count`


Unfortunately, due some unknown reason, this more direct approach doesn't work.
Why?
According to the documentation:  


  In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.


Therefore, a more direct approach would be:

INSERT INTO `table2`(`aid`, `count`)
SELECT `aid`, COUNT(`aid`) AS `count`
FROM `table1`
GROUP BY `aid`
ON DUPLICATE KEY UPDATE `table2`.`count` = VALUES(`count`)