执行开发同学提供的删除数据的sql时报错[Err] 1093 - You can't specify target table 'run_result' for update in FROM clause
查了相关的资料,这个报错的大概意思是不能按照查出的某些值再更新这张表。原来的语句为
DELETE
FROM
run_result
WHERE
fid IN (
SELECT
a.fid
FROM
run_result a
INNER JOIN org b ON a.frunorg = b.fid
WHERE
b.forgpatternid <> 1
)
解决方法:将select出的结果再通过中间表select一遍,修改后的sql为
DELETE
FROM
run_resultt
WHERE
fid IN (SELECT
a.fid
FROM
(
SELECT
a.fid
FROM
run_result a
INNER JOIN org b ON a.frunorg = b.fid
WHERE
b.forgpatternid <> 1
)a
)