How to select the last occurrence that satisfies a query?

Question

MySQL

Hello, please tell me how to solve the following problem.
There is this request:

UPDATE
    sales as s
    join payments_parts pp on pp.FK_sale = s.ID
    join payments pay ON pp.FK_payment = pay.id
SET s.plan_payment_date = pay.payment_date
WHERE s.ID = pp.FK_sale and pay.ID = pp.FK_payment;

The problem is that there can be several rows in the payments table that satisfy the query and the first one gets into the result. I need to get the last one, and ideally by the maximum value of the pay.payment_date field.
Please advise how this can be implemented.
Thanks in advance to all who respond.

Answer 1

If it will ever be useful to someone, then here is a request that satisfies the condition.

UPDATE sales AS s
JOIN ( SELECT pp.FK_sale, MAX(pay.payment_date) payment_date 
       FROM payments_parts pp
       JOIN payments pay ON pp.FK_payment = pay.id
       GROUP BY pp.FK_sale) p ON p.FK_sale = s.ID
SET s.plan_payment_date = p.payment_date;

Answer 2

does it work like that?

UPDATE
    sales as s
SET s.plan_payment_date = (SELECT 
                                                       MAX(pay.payment_date) 
                                                   FROM 
                                                       payments pay
                                                   ,  payments_parts pp
                                                   WHERE pay.ID = pp.FK_payment
                                                          and s.ID = pp.FK_sale 
                                             )

Although he himself would cut off his fingers for such a performer)))