2

Consider the following tables:

=> table1
| aid |
| --- |
| a1  |
| a1  |
| a3  |
| a2  |
| ... |

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

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.