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.

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


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


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
SELECT ProductTypeID FROM ProductTypes);

Here is the result:


I hope this post was useful to you.

We invite you to follow us for more relevant updates.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s