when i execute query delete min field, message appears: "MySQL Error 1093

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

CodePudding user response：

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;

CodePudding user response：

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);