mysql - How to Merge 2 Tables Tentatively Using Replace With Some Unique Data -

i'm trying merge 2 tables single table. using example of tables below need merge table b's purchase_date table a's blank purchase_date. table has email associated purchase date , in table email unique. however, in table b there many email addresses , they're not unique. when merge 2 tables need recent or greatest date table b go table a.

for performance reasons table has ~33.5k rows , table b has ~550k rows.

table email           purchase_date -----------     --------------- test@test.ca     test2@test.ca    test3@test.ca  table b email           purchase_date -----------     --------------- test@test.ca    2016-08-01 0:00:00 test@test.ca    2016-08-03 0:00:00 test2@test.ca   2016-08-13 0:00:00 test2@test.ca   2016-08-14 0:00:00 test2@test.ca   2016-08-15 0:00:00 test3@test.ca   2016-08-27 0:00:00 

you can use update join , subquery max date:

update tablea join (    select email, max(purchase_date) purchase_date    tableb     group email ) b on a.email = b.email set a.purchase_date = b.purchase_date