You can't specify target table '******' for update in FROM

DELETE FROM TABLE WHERE IN(******) 出现错误提示

You can't specify target table '******' for update in FROM

 

 翻译为：不能先select出同一表中的某些值，再update这个表(在同一语句中)

 

错误写法:

DELETE FROM TABLE_A
    　　WHERE TABLE_A.FILED_1 IN (

    　　SELECT FILED_1 FROM TABLE_A
    　　　　WHERE TABLE_A.FILED_1 is not null

　　)

正确写法:

DELETE FROM TABLE_A
    　　WHERE TABLE_A.FILED_1 IN (

　　　SELECT FILED_1 FROM (

 

    　　SELECT FILED_1 FROM TABLE_A
    　　　　WHERE TABLE_A.FILED_1 is not null

　　　)B

　　)

 

即：把结果集当作一个表，自我查询一遍