MySql Update with Inner Join

.Maui

8 years ago

If you need to run an UPDATE in MySql on some rows in a table, but the reference of the condition to use is in another table, the best solution is to use an UPDATE associated with an INNER JOIN.

Here is an example:

UPDATE users
        INNER JOIN
    users_products ON users_products.user_id = users.id 
SET 
    users_products.paid = 1
WHERE
    DATEDIFF(CURDATE(), users.payment_date) > 1;