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.
firstname.lastname@example.org 2016-08-01 0:00:00
email@example.com 2016-08-03 0:00:00
firstname.lastname@example.org 2016-08-13 0:00:00
email@example.com 2016-08-14 0:00:00
firstname.lastname@example.org 2016-08-15 0:00:00
email@example.com 2016-08-27 0:00:00
You can use an
update with a
join and a subquery to get the
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