How to update specific column with values from another table

I have a table called “bug_table” that looks like this:

╔════╦════════════╗
║ id ║ timestamp  ║
╠════╬════════════╣
║ 1  ║ 1589649639 ║
║ 2  ║ 1589649639 ║
║ 3  ║ 1589649639 ║
╚════╩════════════╝

and a table called “bug_revision_table” that looks like this:

╔════════╦═════════════════╦════════════╗
║ bug_id ║ value           ║ timestamp  ║
╠════════╬═════════════════╬════════════╣
║ 1      ║ first revision  ║ 1588587621 ║
║ 1      ║ second revision ║ 1588674021 ║
║ 2      ║ test1           ║ 1588846821 ║
║ 3      ║ rev1            ║ 1589106021 ║
║ 3      ║ rev2            ║ 1589192421 ║
║ 3      ║ rev3            ║ 1589278821 ║
╚════════╩═════════════════╩════════════╝

Now comes the question:

How can I modify the timestamp of each id from the first table with the latest/most recent one from the second table?

As you can see, each bug_id can have multiple revisions with different timestamps. I’m only interested in the latest timestamp.

EDIT: This is how the table should look afterwards:

╔════╦════════════╗
║ id ║ timestamp  ║
╠════╬════════════╣
║ 1  ║ 1588674021 ║
║ 2  ║ 1588846821 ║
║ 3  ║ 1589278821 ║
╚════╩════════════╝

Answer

You could use updated with join

    update bug_table b
    inner join (
        select bug_id, max(timestamp) as max_time
        from  bug_revision_table
        group by bug_id
    ) t on t.bud_id = b.id 
    set b.timestamp = t.max_time

or could be you looking for a unique values for all the ids

update bug_table b
cross join (
    select max(timestamp) as max_time
    from  bug_revision_table
) t 
set b.timestamp = t.max_time

Advertisement

Answer