MySQL: [Err] 1093 - You can't specify target table 'bk' for update in FROM clause
错误的意思说，不能先select出同一表中的某些值，再update这个表(在同一语句中)。 例如下面这个sql：
delete from tbl where id in
(
select max(id) from tbl a where EXISTS
(
select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
)
group by tac
)
改写成下面就行了：
delete from tbl where id in
(
select a.id from
(
select max(id) id from tbl a where EXISTS
(
select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
)
group by tac
) a
)
再如：UPDATE bk_ygsbk AS bk SET REPORT_VISIT = ( SELECT COUNT(*) FROM bk_ygsbk WHERE ZYID = ‘1‘ ) WHERE bk.ZYID = ‘1‘
应改成：UPDATE bk_ygsbk AS bk SET REPORT_VISIT = (SELECT * FROM ( SELECT COUNT(*) FROM bk_ygsbk WHERE ZYID = ‘1‘ ) AS r ) WHERE bk.ZYID = ‘1‘
也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql，mssql和oracle不会出现此问题。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
