mysql批量删除或修改

MYSQL Error Code: 1093 You can't specifytarget table 'x' for update in FROM clause

 

当子查询作为条件，执行delete跟update操作时，会出现：

 

Error Code: 1093 You can't specify targettable 'x' for update in FROM clause

 

作一个简单的示例：

 

CREATE TABLE tbl_a(

id INT,

NAME VARCHAR(50)

);

 

INSERT INTO tbl_a VALUES(1, 'leisure');

INSERT INTO tbl_a VALUES(2, 'leisure2');

 

SELECT * FROM tbl_a;

 

 

执行更新操作

 

UPDATE tbl_a

   SET id = (

       SELECT id FROM tbl_a

       WHERE NAME = 'leisure2'

    )WHERE NAME = 'leisure';

 

这时，如愿见到我们标题上的错误，解决方法如下(橙色字体系关键)：

 

UPDATE tbl_a

   SET id = (

       SELECT id FROM (

           SELECT * FROM tbl_a WHERE NAME = 'leisure2'

       ) xx

   )WHERE NAME = 'leisure';