Delete from a Select in mysql-CodePudding

I'm doing some sql in SQL Fiddle,

I'm trying to do a DELETE from a SELECT.

Normally I first delete the rows, and after I do a SELECT to see if my rows have been succesfully deleted, but now i'm a bit stuck because I want to do that simultaneusly.

This is my SELECT (to select the ID that are not in the table ORDERS):

SELECT customer_id
FROM customers 
WHERE customer_id NOT IN (SELECT customer_id FROM orders)

and this is my DELETE:

DELETE FROM customers
WHERE customer_id IN (1,3,4,9);

Now I've tried this

DELETE FROM customers
WHERE customer_id IN ( SELECT customer_id
    FROM customers 
    WHERE customer_id NOT IN (SELECT customer_id FROM orders)
);

but i have this error:

You can't specify target table 'customers' for update in FROM clause

Thanks for the help!

This is my fiddle link: http://sqlfiddle.com/#!9/f0c94d

CodePudding user response：

The error comes from the second custemrs table, and it is clear with every delete the table changes.

You can rewrite your Query by addind a simple subquery for the customers

CREATE TABLe customers (customer_id int)

CREATE tABLE orders(customer_id int)

DELETE FROM customers
WHERE customer_id IN ( SELECT customer_id
    FROM (SELECT * FROM customers) c 
    WHERE customer_id NOT IN (SELECT customer_id FROM orders)
)

fiddle

But simpoler is to avoid it all together as it is not needed

CREATE TABLe customers (customer_id int)

CREATE tABLE orders(customer_id int)

DELETE FROM customers
WHERE customer_id  NOT IN (SELECT customer_id FROM orders)

fiddle