2

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?

JJD
  • 50,076
  • 60
  • 203
  • 339

5 Answers5

1

This might do what you need:

REPLACE INTO masters
    SELECT v.master_id,v.name,v.added_at 
    FROM versions v 
    WHERE v.added_at = (SELECT MAX(vi.added_at) 
                       FROM versions vi 
                       WHERE  vi.master_id = v.master_id);

Note that this relies on masters having a primary key on id and is MySQL specific.

Jim
  • 22,354
  • 6
  • 52
  • 80
  • This generates duplicates in the *masters* table. – JJD Aug 14 '13 at 12:29
  • 1
    @JJD Does the masters table have a Primary key? I noted that this wont work without one. – Jim Aug 14 '13 at 12:30
  • Thanks! I added `PRIMARY KEY` to the SQL Fiddle. – JJD Aug 14 '13 at 12:33
  • Does this also work when I want to **update specific columns**? I revised my question to include this fact. – JJD Aug 14 '13 at 12:55
  • @JJD No, this won't work since it wont bring over the `note` column. One of the other answers would probably need to be used in that case. – Jim Aug 14 '13 at 12:57
  • And if it is the other way around: Does your solution work when *versions* has **more columns** then *masters* and I only want to update those which exist in *masters*? – JJD Aug 14 '13 at 13:07
  • 1
    @JDD. Yes, the select would only select the columns from versions that you need so versions could have more columns. – Jim Aug 14 '13 at 13:30
1

There is no need to fire subquery twice.

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;
JJD
  • 50,076
  • 60
  • 203
  • 339
Romesh
  • 2,291
  • 3
  • 24
  • 47
  • I removed the parts matching the specific `..id = 2`. Running your example then misses to update *masters* row with `id` = `4`, `d-old` for some reason. – JJD Aug 14 '13 at 12:37
  • 1
    Now check [this link](http://sqlfiddle.com/#!2/e56eb9/1). if its correct then i will modify the post – Romesh Aug 14 '13 at 13:27
  • Looks good. It seems to also work with additional columns as I introduced them to my question in the meantime. Please update your answer. Btw: Typo in "Fiddel". – JJD Aug 14 '13 at 13:39
  • @JJD I have modified my post. Look at it. – Romesh Aug 14 '13 at 13:48
  • Your solution has the advantage that the *masters* table can contain **additional columns**. (+1) Though explicitely defining each pair such as `m.name = v.name` makes it a bit unflexible for changing columns in the future. – JJD Aug 14 '13 at 14:01
  • If my post is what you were searching for then you can accept it. – Romesh Aug 14 '13 at 14:16
  • I intend doing this and I am thankful for your help. But let's wait if somebody comes up with a solution using `JOIN` and without using the pair definition as I mentioned. I will leave the question open for at least one day. – JJD Aug 14 '13 at 14:26
  • Instead of `,` simply replace with `join` an check – Romesh Aug 14 '13 at 14:29
  • I am not sure if I get you right.. I was talking about `INNER JOIN` or similar... – JJD Aug 14 '13 at 16:41
0

Can't test it on mysql right now, but this should work on MSSQL

UPDATE masters AS m
SET 
  m.name = 
     (SELECT v.Name FROM versions AS v WHERE v.id = m.id AND v.added_at = 
     (SELECT MAX(v2.added_at) FROM versions As v2 WHERE v2.id = v.id))
  m.added_at = 
     (SELECT v.added_at FROM versions AS v3 WHERE v3.id = m.id AND v3.added_at = 
     (SELECT MAX(v4.added_at) FROM versions As v4 WHERE v4.id = v3.id))
Bruellhusten
  • 318
  • 1
  • 5
  • I would like to avoid running a separate `SELECT` statements for each individual column. – JJD Aug 14 '13 at 12:31
0
update masters set 
name = (SELECT name FROM versions
       WHERE master_id = masters.id
       ORDER BY added_at DESC
       LIMIT 1),
added_at = (SELECT added_at FROM versions
        WHERE master_id = masters.id
        ORDER BY added_at DESC
        LIMIT 1)
where id = 2;
Bere
  • 1,627
  • 2
  • 16
  • 22
  • I would like to avoid running a separate `SELECT` statements for each individual column. – JJD Aug 14 '13 at 12:37
0

This might work for you, try this:

UPDATE masters m 
SET m.name = (SELECT v.name FROM versions v WHERE
m.id = v.master_id ORDER BY v.added_at DESC LIMIT 1), 
m.added_at =
(SELECT v.added_at FROM versions v WHERE m.id = v.master_id ORDER BY
v.added_at DESC LIMIT 1);
Jivan
  • 1,300
  • 6
  • 21
  • 33
  • I would like to avoid running a separate `SELECT` statements for each individual column. – JJD Aug 14 '13 at 12:38