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