mysql:You can't specify target table 'bpm_tksign_data' for update in FROM clause

UPDATE bpm_tksign_data
SET iscompleted = 1
WHERE
    actinstid = '10000002433415'
AND nodeid = 'SignTask1'
AND iscompleted = 0
AND batch = (
    SELECT
        max(a.batch) m
    FROM
        bpm_tksign_data a
    WHERE
        a.actinstid = '10000002433415'
    AND a.nodeid = 'SignTask1'
);

这种写法,mysql各个版本都不支持。

UPDATE bpm_tksign_data
SET iscompleted = 1
WHERE
    actinstid = '10000002433415'
AND nodeid = 'SignTask1'
AND iscompleted = 0
AND batch = (
    SELECT
        max(a.batch) m
    FROM
        (SELECT * FROM bpm_tksign_data) a
    WHERE
        a.actinstid = '10000002433415'
    AND a.nodeid = 'SignTask1'
);

 这种写法,mysql 5.6支持,mysql 5.7不支持。

UPDATE bpm_tksign_data
SET iscompleted = 1
WHERE
    actinstid = '10000002433415'
AND nodeid = 'SignTask1'
AND iscompleted = 0
AND batch = (
    SELECT
        p.m
    FROM
        (
            SELECT
                max(a.batch) m
            FROM
                bpm_tksign_data a
            WHERE
                a.actinstid = '10000002433415'
            AND a.nodeid = 'SignTask1'
        ) p
);

这种写法,mysql各个版本都可以支持。

posted @ 2016-03-08 12:06  云中飞鱼  阅读(296)  评论(0编辑  收藏  举报