MySQL you can ' t specify target table ' sys_org_relation ' for update in FROM clause delete table condition cannot directly contain the table

MySQL in your can ' t specify target table for update in 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).

Example: DELETE from Sys_org_relation where Porgid in (

Select Porgid from Sys_org_relation R where r.corgid= ' customer ID ' and relationtype=1112

) and and relationtype=1213;

The sys_org_relation table condition to be manipulated in this statement is also the result of a query from the Sys_org_relation table, which throws you the can ' t specify target table ' Sys_org_relation ' for Update in FROM clause exception

Workaround:

An intermediate conversion table is required, which means that the result of the select is passed through the intermediate table select again, which avoids the error. Note that this issue only occurs in Mysql,mssql and Oracle does not occur.

Should be changed to the following:

DELETE from Sys_org_relation where Porgid in (

Select A.corgidfrom (

Select Corgidfrom sys_org_relation R where r.porgid= ' customer ID ' and relationtype=1112

) A

) and and relationtype=1213;

MySQL you can ' t specify target table ' sys_org_relation ' for update in FROM clause delete table condition cannot directly contain the table