mysql异常:You can't specify target table 'my' for update in FROM clause

如果需要数据库中的数据与现有的数据进行操作 在mysql中,需要在查询原有数据时,需要将其作为中间表然后进行操作. oracle则不需要 举例 : 表结构 如下是错误的

INSERT INTO my (id, iden, NAME, age) VALUES ( 2, '2', '2', 1 + IFNULL( ( SELECT age FROM my WHERE id = 1 ), 0 ) );

报错信息:

[SQL]INSERT INTO my (id, iden, NAME, age) VALUES ( 2, '2', '2', 1 + IFNULL( ( SELECT age FROM my WHERE id = 1 ), 0 ) ); [Err] 1093 - You can't specify target table 'my' for update in FROM clause

mysql正确方式

INSERT INTO my (id, iden, NAME, age) VALUES ( 2, '2', '2', 1 + IFNULL( ( SELECT a.age FROM (SELECT age FROM my WHERE id = 1) a ), 0 ) );

OK

[SQL]INSERT INTO my (id,iden, NAME, age) VALUES ( 2, '2', '2', 1 + IFNULL( (SELECT a.age from (SELECT age FROM my WHERE id = 1) a), 0 ) ); 受影响的行: 1 时间: 0.007s