INSERT... SELECT... WHERE... ON DUPLICATE... mysql query

Question

Please help. I'm trying to update this one table with the current count of assets our products have. If the product already exist in the table, it should update the most updated count of the product. However, using the query below, mysql is returning me

"ERROR 1111 (HY000): Invalid use of group function".

I can't determine what's my error or if it is really valid to use count in function 'on duplicate key':

INSERT INTO report_count_assets
    SELECT products.product_id, 
    count(product_assets.asset_id),
    count(case when assets.asset_type_id=1 THEN 1 END), 
    count(case when assets.asset_type_id=2 THEN 1 END), 
    count(case when assets.asset_type_id=3 THEN 1 END), 
    count(case when assets.asset_type_id=11 THEN 1 END) 
    FROM products 
    LEFT JOIN product_assets USING (product_id) 
    LEFT JOIN assets USING (asset_id)
    WHERE products.brand_id=671

ON DUPLICATE KEY UPDATE
    asset_count = count(product_assets.asset_id),
    asset_type_image = count(case when assets.asset_type_id=1 THEN 1 END), 
    asset_type_video = count(case when assets.asset_type_id=2 THEN 1 END), 
    asset_type_sound = count(case when assets.asset_type_id=3 THEN 1 END), 
    asset_type_install = count(case when assets.asset_type_id=11 THEN 1 END);

Answer 1

I don't think you can use aggregate functions in the ON DUPLICATE. MySQL sees your SQL sort of like this:

insert into report_count_assets
expr
on duplicate key update
...

The ON DUPLICATE doesn't know what's going on in expr, it only knows that it has a single duplicate row to deal with. Without knowing what's going on inside expr, there is no context for the counts to operate on. Also, you're supposed to use values in the UPDATE:

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement.

And values(count(x)) is not valid syntax. But values(column_name) is valid so this should work:

INSERT INTO report_count_assets
(product_id, asset_count, asset_type_image, asset_type_video, asset_type_sound, asset_type_install)
    SELECT products.product_id, 
    count(product_assets.asset_id),
    count(case when assets.asset_type_id=1 THEN 1 END), 
    count(case when assets.asset_type_id=2 THEN 1 END), 
    count(case when assets.asset_type_id=3 THEN 1 END), 
    count(case when assets.asset_type_id=11 THEN 1 END) 
    FROM products 
    LEFT JOIN product_assets USING (product_id) 
    LEFT JOIN assets USING (asset_id)
    WHERE products.brand_id=671
ON DUPLICATE KEY UPDATE
    asset_count = values(asset_count),
    asset_type_image = values(asset_type_image), 
    asset_type_video = values(asset_type_video), 
    asset_type_sound = values(asset_type_sound), 
    asset_type_install = values(asset_type_install);

I had to guess the name of the product_id column in report_count_assets.

If that doesn't work (as apparently it doesn't), then you can do it the hard way by precomputing the SELECT. Create a temporary table:

create temporary table t (
    product_id int,
    product_count int,
    assets1 int,
    assets2 int,
    assets3 int,
    assets11 int
)

Populate it:

INSERT INTO t (product_id, product_count, assets1, assets2, assets3, assets11)
SELECT products.product_id, 
count(product_assets.asset_id),
count(case when assets.asset_type_id=1 THEN 1 END), 
count(case when assets.asset_type_id=2 THEN 1 END), 
count(case when assets.asset_type_id=3 THEN 1 END), 
count(case when assets.asset_type_id=11 THEN 1 END) 
FROM products 
LEFT JOIN product_assets USING (product_id) 
LEFT JOIN assets USING (asset_id)
WHERE products.brand_id=671

And then use that temporary table to do the insert that you really want to do:

insert into report_count_assets
    select product_id, product_count, assets1, assets2, assets3, assets11
    from t
on duplicate key update
    asset_count = values(product_count),
    asset_type_image = values(assets1),
    asset_type_video = values(assets2),
    asset_type_sound = values(assets3),
    asset_type_install = values(assets11)