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

I needed to change a…

Login

Blog History

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"

1
2
3
4
5
6
7
8
9
10
11
12
13
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.