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 count
s 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)