How to update multiple columns based on values from an associated table?

Question

There are two tables named masters and versions. The versions table holds entries of the master table at different points in time.

-------------------------
 masters
-------------------------
 id |  name |  added_at
----+-------+------------
  1 | a-old | 2013-08-13
  2 | b-new | 2012-04-19
  3 | c-old | 2012-02-01
  4 | d-old | 2012-12-24

It is guaranteed that there is at least one versions entry for each masters entry.

---------------------------------------------   
 versions
---------------------------------------------
 id |  name |   added_at | notes | master_id
----+-------+--------------------------------
  1 | a-new | 2013-08-14 | lorem |         1
  1 | a-old | 2013-08-13 | lorem |         1
  2 | b-new | 2012-04-19 | lorem |         2
  3 | c-old | 2012-02-01 | lorem |         3
  4 | d-new | 2013-02-20 | lorem |         4
  5 | d-old | 2012-12-24 | lorem |         4

The tables can also be found in this SQL Fiddle.
The latest version of each master record can be selected as shown in this example for masters record 2:

SELECT * FROM versions
WHERE master_id = 2
ORDER BY added_at DESC
LIMIT 1;

How can I update each record of the masters table with its latest version in one command? I want to overwrite the values for both the name and added_at columns. Please note, there are additional columns in the versions table which do not exist in the masters table such as notes.

Can the update been done with a JOIN so it performs fast on larger tables?

Answer 1

There is no need to fire subquery twice.

• Here is SQLFiddle Demo

Below is the update statement

update masters m, (
  select id, name, added_at, master_id 
  from versions 
  order by added_at desc
) V
set
  m.name = v.name, 
  m.added_at = v.added_at     
where v.master_id = m.id;