Skip to content
Advertisement

Take maximum time during update left join mysql

I am doing an update left join on ID and receiver.

Left table
Id | Receiver | Order Time | guid | item
12 | abc      | 12:35      | NULL | NULL


Right table
Id | Receiver | Time taken | guid  | item
12 | abc      | 50seconds  | yuhb2 | 0
12 | abc      | 189seconds | vef3  | 2

How can I make the query to pick the record with max time taken?

update tableA a left join TableB b
ON  a.ID=b.ID
AND    a.receiver = b.receiver
set
a.items= b.items
where a.guid is null;

Expected output :

Left table
Id | Receiver | Order Time | guid | item
12 | abc      | 12:35      | vef3 |  2

Advertisement

Answer

I have added another column (id_primary) in right_table. To get the maximum use this subquery (select id_primary from right_table order by time_taken desc limit 1) at end

 update left_table a left join right_table b
    ON  a.id= b.id
    set
    a.item= b.item,
    a.guid= b.guid
    where a.guid is null
    and b.id_primary = (select id_primary from right_table order by time_taken desc limit 1 )
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement