MySQL can ' t specify target table for update in from Clau

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

The

code is as follows:


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 has count (1) >1


        )


GROUP BY TAC


)

Rewrite it to the following line:

The

code is 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 has count (1) >1


        )


GROUP BY TAC


) A


)

That is, the result of the select is then select through the middle table, which avoids the error. Note that this problem occurs only in Mysql,mssql and Oracle.