MySQL UPDATE, MAX, JOIN query

Question

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 want to keep the top product_id rating for each manufacturer in the pivot table: -

 summary_table manufacturer, max_score ford, 1 toyota, 3 

So far I have: -

 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; 

You have problems ... All help is greatly appreciated.

Answer 1

As I understand it, I think this will work, I used MAX(Product_ID) only to allow any duplicates, where 2 products for the same production can have the same rating, and both of them are the highest rating. You might want to allow duplicates in different ways.

 UPDATE summary_table SET max_score = ( SELECT MAX(m.Product_ID) [MaxScoreProductID] FROM manu_table m INNER JOIN product_table p ON m.Product_ID = p.Product_ID INNER JOIN ( SELECT Manufacturer, MAX(Score) [MaxScore] FROM manu_table m LEFT JOIN product_table p ON m.Product_ID = p.Product_ID GROUP BY Manufacturer ) ms ON ms.Manufacturer = m.Manufacturer AND ms.MaxScore = p.Score WHERE m.Manufacturer = summary_table.Manufacturer GROUP BY m.Manufacturer ) 

Answer 2

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 request sets product_id for maximum evaluation:

 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