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:

UPDATE t1 JOIN ( 
 SELECT t2.t1id, MAX(t2.thedate) maxdate FROM  t2 GROUP BY t2.t1id
) AS agg ON t1.id=agg.t1id
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!


Comments

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