How to update fields using grouped data from another table

For optimisation purposes you have decided to create a new field in a table, and that field needs to contain a computed value of rows from another table.

Say that your first table will contain the maximum date from the second table, and that there is a one-to-many relationship: field t1id in the second table refers to field id in the first table. Here's the SQL to update all max_date fields in the first table:

 SELECT t2.t1id, MAX(t2.thedate) maxdate FROM  t2 GROUP BY t2.t1id
) AS agg ON
SET t1.max_date=agg.maxdate

I hope that this proves useful; let me know if it does!

Disclaimer: I won't be help responsible for any loss of data or time you suffer as a result of following my advice!


It's quiet in here...Add your comment