You can’t specify target table for update….

This article mainly introduces the MySQL you can’t specify target table for update in from clause error resolution, need friends can refer to

The error of you can’t specify target table for update in from clause in MySQL means that you can’t select some values in the same table and then update the table (in the same statement). For example, the following SQL:

delete from tbl where id in 
(
        select max(id) from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)&>1
        )
        group by tac
)

Just rewrite it as follows:

delete from tbl where id in 
(
    select a.id from 
    (
        select max(id) id from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)&>1
        )
        group by tac
    ) a
)

That is to say, select the result through the middle table again, so as to avoid the error. Note that this problem only occurs in mysql, and will not occur in MSSQL and Oracle.

Similar Posts: