MySQL updating table with values selected from same table in one go I want to update quite a few values in a table. Said values already exist in another table.

I'm selecting said values using the following query:

SELECT c.last_purchase, 
       so.customer_id,
       so.modified_at
  FROM customer c 
 INNER JOIN sale_order so  ON c.customer_id = so.customer_id 
                          AND c.country = so.country 
 WHERE so.created_at &gt; c.last_purchase


So far so good. But my sql skills aren't too great(hey, I'm being honest here).

The correct values are so.modified_at. I want to update c.last_purchase to match so.modified_at. 

Originally I was thinking of using a WHEN THEN combination. Or perhaps some sort of stored procedure.

I could this by using a bash script or any other programming language that knows how to connect to a database. But I'd like, if possible, to do this strictly using MySQL. If not possible I'll have to settle for a bash script(I already wrote one).

Honestly, I have no idea where to start.

Any pointing in the right direction is more than appreciated.
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;