Introduction to the restrictions of delete from where subquery in mysql

This article brings you an introduction to the restrictions of delete from where subqueries in mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. help.

1. When using mysql to perform a delete from operation, if the FROM clause of the subquery and the update/delete object use the same table, an error will occur. (Related recommendations: 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. "This limitation can be solved in most cases by adding an extra layer of select alias tables, 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... The table cannot use aliases

mysql> delete from table a where a.id in (1,2);(Syntax error)
mysql> select a.* from table a where a. id in (1,2);(Execution successful)

The above is the detailed content of Introduction to the restrictions of delete from where subquery in mysql. For more information, please follow other related articles on the PHP Chinese website!