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 )