I have two tables: -
manu_table
product_id, manufacturer
1, ford
2, ford
3, toyota
product_table
product_id, score
1, 80
2, 60
3, 40
I'd like to store the top scoring product_id for each manufacturer in a summary table: -
summary_table
manufacturer, max_score
ford, 1
toyota, 3
So far I've got: -
UPDATE summary_table st
SET max_score = (
SELECT product_id
FROM (
SELECT manufacturer, product_id, max(score) as ms
FROM manu_table
LEFT JOIN product_table USING (product_id)
group by product_id) t)
WHERE st.manufacturer = manu_table.manufacturer;
Having troubles...All help is appreciated greatly.
Try this query -
UPDATE summary_table st
JOIN (
SELECT mt.manufacturer, MAX(pt.score) max_score FROM manu_table mt
JOIN product_table pt
ON mt.product_id = pt.product_id
GROUP BY mt.manufacturer) t
ON t.manufacturer = st.manufacturer
SET st.max_score = t.max_score
This query sets product_id for max score:
UPDATE summary_table st
JOIN (SELECT t1.* FROM
(SELECT mt.*, pt.score FROM manu_table mt JOIN product_table pt ON mt.product_id = pt.product_id) t1
JOIN (
SELECT mt.manufacturer, MAX(pt.score) max_score FROM manu_table mt
JOIN product_table pt
ON mt.product_id = pt.product_id
GROUP BY mt.manufacturer
) t2
ON t1.manufacturer = t2.manufacturer AND t1.score = t2.max_score
) t
ON t.manufacturer = st.manufacturer
SET st.max_score = t.product_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With