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”

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

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

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