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

this [Question](http://stackoverflow.com/questions/2330840/mysql-invalid-use-of-group-function) may help u — xkeshav, Feb 04 '12 at 07:07
Hi @diEcho, I'm not particularly looking for what products have this certain numbers in their assets. My concern is the update part of the query when the product is already present in the table. I don't know how select-join query can be implemented on 'ON DUPLICATE KEY UPDATE' — Majinbibo, Feb 04 '12 at 07:11

mu is too short · Accepted Answer · 2012-02-07T03:34:35.527

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)

Hi @mu is too short, thank you so much! You saved a lot of blood of me. — Majinbibo, Feb 04 '12 at 07:27
@Jan-DeanFajardo: I have much nicer solution this morning if you're interested. — mu is too short, Feb 04 '12 at 18:13
@Jan-DeanFajardo: You just use `values` inside the `ON DUPLICATE` section as in my update. I don't know what I was thinking with all that temp table stuff, [target fixation](http://en.wikipedia.org/wiki/Target_fixation) perhaps. I blame answering right before going to bed :) — mu is too short, Feb 06 '12 at 07:16
well actually what you did about the temp table solved my problem. The values() inside ON DUPLICATE didn't work since it's only getting the current value of fields and not the updated value I'm getting from the SELECT clause. — Majinbibo, Feb 07 '12 at 03:26
@Jan-DeanFajardo: I put the temp table approach back, what ever works :) — mu is too short, Feb 07 '12 at 03:35

score 0 · Answer 2 · answered Feb 04 '12 at 07:27

0

Try to group by product_assets.asset_id:

.
.
WHERE products.brand_id=671
group by product_assets.asset_id
ON DUPLICATE KEY UPDATE
    asset_count = count(product_assets.asset_id)
.
.

answered Feb 04 '12 at 07:27

Somnath Muluk

55,015
38
216
226

Also see this [link](http://stackoverflow.com/questions/1712240/mysql-insert-into-on-duplicate-key-with-select-statement-issue) – Somnath Muluk Feb 04 '12 at 07:27

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

2 Answers2

Linked