Error during DELETE: You can’t specify target table '...' for update in FROM clause

Question

I’m trying to delete data (where the stock is equal to zero and the discount value is less than the total discount value average) with this query:

DELETE FROM veiculo WHERE estoquetotal = 0 AND valordesconto < (SELECT AVG(valordesconto) FROM veiculo);

But it is returned to me "You can't specify target table 'veiculo' for update in FROM clause".

I’ve searched for a solution here, but I’m not able to apply any of them in my query (I’m new to Mysql database). Of the answers I researched, they had to do with JOIN. Thank you in advance.

Answer 1

I have read and answered this somewhere, that I know mysql does not accept data change with query in the same table.

then try to change your query so that user a variable instead of doing

(SELECT AVG(valordesconto) FROM veiculo); 

store the value in a variable and then use in consultation.

@valordesconto =  (SELECT AVG(valordesconto) FROM veiculo);

DELETE FROM veiculo WHERE estoquetotal = 0 AND valordesconto < @valordesconto ;