How to update fields using grouped data from another table

By John Swindells 29 Jan 2011 | Comment
| Share:
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!

Share:

Comments

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