CODEWITHSUNDEEP
mysqlsql
Jason
Jason

Reputation: 373

MySQL UPDATE, MAX, JOIN query

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.

Upvotes: 8

Views: 3156

Answers (2)

Devart
Devart

Reputation: 122002

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

Upvotes: 4

GarethD
GarethD

Reputation: 69789

As I understand the problem I think this would work, I have used MAX(Product_ID) just to resolve any duplicates, where 2 products for the same manufacture might have the same score and both be the highest score. You may want to resolve duplicates differently.

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
        )

Upvotes: 2

Related Questions