MYSQL – Performing an UPDATE on a table you are SELECTing from

Written by James McDonald

June 27, 2018

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

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Squarespace Image Export

To gain continued access to your Squarespace website images after cancelling your subscription you have several...

MySQL 8.x GRANT ALL STATEMENT

-- CREATE CREATE USER 'tgnrestoreuser'@'localhost' IDENTIFIED BY 'AppleSauceLoveBird2024'; GRANT ALL PRIVILEGES ON...

Exetel Opt-Out of CGNAT

If your port forwards and inbound and/or outbound site-to-site VPN's have failed when switching to Exetel due to their...