What are the limitations of delete from where subqueries in mysql

Editor to share with you what are the limitations of the delete from where sub-query in mysql. I hope you will learn a lot after reading this article. Let's discuss it together.

1. When using mysql for delete from operation, if the FROM sentence of the subquery and the update / delete object use the same table, an error will occur. (recommended: MySQL tutorial)

Mysql > DELETE FROM 'tab' where id in (select min (id) from tag GROUP BY field1,field2 HAVING COUNT (id) > 1)

Error: You can't specify target table 'tab' for update in FROM clause. (the target table 'tab' cannot be specified for updates in the FROM clause)

In most cases, the limitation of "the same table" can be solved by adding an extra layer of select alias table, like this.

DELETE FROM 'tab' where id in (select id from (select max (id) from' tab' GROUP BY field1,field2 HAVING COUNT (id) > 1) ids)

2.delete from table... Aliases cannot be used for table

Mysql > delete from table a where a.id in (1 > 2); (syntax error)

Mysql > select a. * from table a where a.id in (1); (executed successfully)

After reading this article, I believe you have a certain understanding of the restrictions on delete from where sub-query in mysql. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!