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

Question

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

Navigation

#1 by (3 votes)

2

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

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

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

I've already researched 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.

mysql

asked by anonymous 03.08.2017 / 15:38

1 answer

View Mapping with Entity Framework Insert multiple with PDO php and MySql

score 3 · Accepted Answer

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

Then try changing your query so that user a variable instead of doing

(SELECT AVG(valordesconto) FROM veiculo);

store the value in a variable and then use it in the query.

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

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