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

Related Reading

Web Development Survey!
Which web technologies would you like to see the back of, and why?


Popular Searches