Update last child id in parent table using mysql
- by Sam Saffron
Given the following tables:
Topic
id, last_updated_child_id
Response
id, topic_id, updated_at
How do I update the Topic table so the last_updated_child_id is equal to the latest response id (based on date).
So for example given:
Topic
id last_updated_child_id
-- -----------------------
1 null
2 null
3 null
Response
id topic_id updated_at
-- ---- ----
1 1 2010
2 1 2012
3 1 2011
4 2 2000
I would like to execute an UPDATE statement that would result in the Topic table being:
id last_updated_child_id
-- -----------------------
1 2
2 4
3 null
Note: I would like to avoid temp tables if possible and am happy for a MySQL specific solution.
