when i execute query delete min field, message appears: "MySQL Error 1093 - Can't specify target table for update in FROM clause "

user_16983635 0
Viewed 34

i want to delete a product with the min "sale_quantity" in table product.

and This is my query:

use warehousingaquarium;
delete from product
where product.sale_quantity = (select min(p2.sale_quantity) 
                from product p2);
SET SQL_SAFE_UPDATES = 0;

It gives me the following error:

Error Code: 1093. You can't specify target table 'product' for update in FROM clause

enter image description here

mysql sql database
2 Answers

One way to get around this problem would be to phrase your delete as a join:

DELETE p1
FROM product p1
INNER JOIN
(
    SELECT MIN(sale_quantity) AS min_sale_quantity
    FROM product
) p2
    ON p2.min_sale_quantity = p1.sale_quantity;
user_18632290

For updating and deleting from such clause where subquery contains the same table that you are deleting from, a temporary table is required so that you don't delete the data where you are selecting from. In order to make your query work, you need to introduce a temp table:

USE,

delete from product
where product.sale_quantity = (select min(p2.sale_quantity) 
                from (Select * from product) p2);
user_80357590
Related