mysql update cell value with join from other table if datetime value is bigger

Question

I have 2 tables:

A table: (where every code is unique, occurs only one time)

id |      code     |       datetime      |  
1  | 2574857458745 | 2017-05-20 20:15:30 | - update this code datetime   
2  | 6554995949445 | 2017-07-13 11:17:40 |  
3  | 8214687655556 | 2017-04-27 21:26:55 |  
4  | 3354551848451 | 0000-00-00 00:00:00 |

B table: (where codes occurs many times)

id  |      code     |       datetime      |  
26  | 2574857458745 | 2018-07-14 16:24:20 |  - occurs here 2 times  
47  | 6554995949445 | 2018-09-06 17:35:44 |  
64  | 8214687655556 | 2018-03-09 22:06:12 |  
57  | 2574857458745 | 2018-11-12 23:57:35 |  - update only with the latest datetime

So the table A first row to be:

id |      code     |       datetime      |  
1  | 2574857458745 | 2018-11-12 23:57:35 |

and so on, search every code and if exist in the B table then update with the latest datetime

It is possible to achieve this with mysql update with join? If yes then how? Or other ideas?

Answer 1

you can use update with join:

UPDATE tablea a
JOIN (SELECT code, MAX(datetime) as maxdt FROM tableb GROUP BY code) b
ON a.code = b.code
SET a.datetime = b.maxdt;

Note: If tableA's datetime is higher than tableB's latest datetime, this will update anyway