You can't specify target table 'EMP' for update in FROM clause 解决办法

错误语句 1

DELETE FROM EMP

    WHERE EMPNO IN (

        SELECT EMPNO FROM EMP E, DEPT D

            WHERE E.DEPTNO = D.DEPTNO AND D.LOC = 'NEW YORK'

    );

错误语句 2

UPDATE COPY_EMP

    SET SAL = (

        SELECT AVG(SAL) FROM COPY_EMP

    )

WHERE SAL IS NULL;

 

原因：如果子查询的语句和更新，删除在同一张表上，MY SQL会出现错误，因此要双重子查询

 

正确语句 1

DELETE FROM EMP

    WHERE EMPNO IN (

        SELECT A.EMPNO FROM (

            SELECT EMPNO FROM EMP E, DEPT D

                WHERE E.DEPTNO = D.DEPTNO AND D.LOC = 'NEW YORK'

    ) AS A

);

 

正确语句 2

UPDATE COPY_EMP

    SET SAL = (

        SELECT A.SAL FROM (

            SELECT AVG(SAL) AS SAL FROM COPY_EMP

        ) A

)

WHERE SAL IS NULL;