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

时间:2021-12-28 16:51:29
UPDATE bpm_tksign_data
SET iscompleted = 1
WHERE
actinstid = ''
AND nodeid = 'SignTask1'
AND iscompleted = 0
AND batch = (
SELECT
max(a.batch) m
FROM
bpm_tksign_data a
WHERE
a.actinstid = ''
AND a.nodeid = 'SignTask1'
);

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

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

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

UPDATE bpm_tksign_data
SET iscompleted = 1
WHERE
actinstid = ''
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 = ''
AND a.nodeid = 'SignTask1'
) p
);

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