MySQL UPDATE JOIN with Aggregate Function in SET

When relational model tables are normalized, as was discussed in a previous post, it may be necessary to join them to obtain the correct records or to update the records that correspond to another table. The notion is the same for all relational products, however the syntax in this instance is slightly different. Aggregate methods are required when we occasionally need to replace a column value with the largest or lowest value, or the total of values from different tables.

Syntax:
UPDATE table1 A
,(SELECT columns and aggregate values FROM table2) AS B
JOIN condition
SET A.column1 = B.aggregateValue
WHERE condition

Example:
ProductTypeID,ProductType
1,General
2,Grocery
3,Electrical
4,Electronic

ProductID, ProductTypeID, ProductName
101, 0, Britannia Bisc
102, 0, Cadbury Choco
103, 4, Samsung Charger
104, 2, Sugar
105, 3, Bulb 40w

Requirement:
In this example, the data is either entered before the constraints are applied or the data has null or zero values before the constraints being applied. As you are aware, constraints can be added without first validating the existing data.

As a result, the information is not unlikely; it may be the result of legacy databases or data that was imported using flat files; we frequently encounter such data in our tables. In this situation, it is required to map items to the product type “General” while updating the zero values with the lowest/first key value in the database. Also, needs to ensure the right Product Type IDs shouldn’t be updated.

UPDATE ProductDetails PD
,(SELECT MIN( ProductTypeID ) AS ProductTypeID FROM ProductTypes) AS PT
SET PD.ProductTypeID = PT.ProductTypeID
WHERE PD.ProductTypeID NOT IN(
SELECT ProductTypeID FROM ProductTypes);