I needed to change a record in a customer table to be inactive if they hadn’t received a delivery in the last 3 months.

I got stuck with “Error Code: 1093. You can’t specify target table ‘customers’ for update in FROM clause”

UPDATE customers c1 SET c1.active = 0
WHERE c1.id NOT IN  ( 
	SELECT id FROM ( 
		/* you need to wrap the select in another select */
		SELECT c2.id  from deliveries
		JOIN deliveries_customers ON deliveries_customers.delivery_id = deliveries.id
		JOIN customers c2 ON c2.id = deliveries_customers.customer_id
		WHERE deliveries.distribution_centre_id = '2' 
			AND deliveries.last_delivery >= '2018-03-01'
		GROUP BY c2.customer ) x
                /* need to alias the select too (x) */
	)  
AND c1.distribution_centre_id = '2' ;

Refs: https://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

https://stackoverflow.com/a/14302701/9230077