MySQL updating table with values selected from same table in one go
As per my understanding, you need to update last_purchase field with modified_at field by joining customer table and sale_order table using the conditions you mentioned in your query.

Then try the following query

UPDATE customer   AS c
  JOIN sale_order AS s 
   SET c.last_purchase = s.modified_at
 WHERE c.customer_id = s.customer_id
   AND c.country = s.country
   AND s.created_at &gt; c.last_purchase;