dbodnar dbodnar - 1 year ago 69
MySQL Question

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

I'm trying to merge two tables into a single table. Using the example of the tables below I need to merge table B's Purchase_Date into Table A's blank Purchase_Date. Table A has an email associated with the purchase date and in table A the email is unique. However, in table B there are many email addresses and they're not unique. When I merge the two tables together I need the most recent or greatest date from table B to go into table A.

For performance reasons table A has ~33.5k rows and table B has ~550k rows.

Table A
Email Purchase_Date
----------- ---------------

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

Answer Source

You can use an update with a join and a subquery to get the max date:

update tablea a
join (
   select email, max(purchase_date) purchase_date
   from tableb 
   group by email
) b on a.email = b.email
set a.purchase_date = b.purchase_date
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download