SQL query for Update table using Join & Group By

Question

I have used this to get Data

SELECT customers.*, COUNT(invoice.payment_status) AS pending_invoices,sum(invoice.total ) AS total_pending_amount 
FROM customers JOIN invoice ON (invoice.customer_id = customers.id) 
WHERE invoice.payment_status = 'P' AND invoice.status = 'A' AND payment_followup_date IS NOT NULL AND payment_followup_date <= '2022-03-24 23:59' 
GROUP BY invoice.customer_id 
ORDER By payment_followup_date DESC

Now I am looking for a Query to perform an update on customers to add the current DateTime into payment_payment_followup_date and payment status = P for those customers who have more than 3 pending invoices

Answer 1

Try this:

UPDATE customers c
    INNER JOIN (
        SELECT 
            customer_id, COUNT(payment_status) pending_invoices
        FROM invoice
        WHERE payment_status = 'P' AND status = 'A'
        GROUP BY customer_id
        HAVING COUNT(payment_status) > 3
    ) i ON c.customer_id = i.customer_id
SET c.payment_payment_followup_date = CURRENT_TIMESTAMP()
    c.payment_status = 'P' 
WHERE c.payment_followup_date IS NOT NULL 
    AND c.payment_followup_date <= '2022-03-24 23:59'