Mysql:You can’t specify target table ‘table_name’ for update in FROM clause

发表时间:2011-08-29 15:54 | 分类:Mysql | 浏览:574 次

如果你习惯mssql的写法,那么在写mysql的时候,可能会发现一个有很意思的问题。比如现在我写了这样一个sql:

update dhc_odds set cfirst_matchdatetime=(select cmatchdatetime from(select min(cmatchdatetime) from dhc_odds where cbatch_no='WED002') as b )
where cbatch_no='WED002'

当运行后,mysql就会提示说“You can't specify target table 'dhc_odds' for update in FROM clause”,具体的意思就是说不能在同一语句中先select出同一表中的某些值,再update这个表。根据网上高手的经验,可以把上面这个语句写成下面这样就行:

update dhc_odds A ,(select min(cmatchdatetime) AS MA from dhc_odds where cbatch_no='WED002') B
set A.cfirst_matchdatetime=B.MA
Where A.cbatch_no='WED002'

另外一个mysql的例子:

update wms_cabinet_form set cabf_enabled=0
where cabf_id in (
SELECT wms_cabinet_form.cabf_id FROM wms_cabinet_form
Inner Join wms_cabinet ON wms_cabinet_form.cabf_cab_id = wms_cabinet.cab_id
Inner Join wms_cabinet_row ON wms_cabinet.cab_row_id =wms_cabinet_row.row_id
where wms_cabinet_row.row_site_id=27 and wms_cabinet_form.cabf_enabled=1)

上面这个sql运行的时候同样会出现You can't specify target table这样类似的错误提示,我们也可以把他改成这样:

update wms_cabinet_form set cabf_enabled=0 where cabf_id in (
SELECT a.cabf_id FROM (select tmp.* from wms_cabinet_form tmp) a
Inner Join wms_cabinet b ON a.cabf_cab_id = b.cab_id
Inner Join wms_cabinet_row c ON b.cab_row_id = c.row_id
where c.row_site_id=29 and a.cabf_enabled=1)

重点在 SELECT a.cabf_id FROM (select tmp.* from wms_cabinet_form tmp) a ,select tmp.* from wms_cabinet_form tmp 作为子集,然后再select a.cabf_id FROM 子集,这样就不会select和update都是同一个表。

本文标签:

本文链接:http://www.sijitao.net/527.html

欢迎您在本博客中留下评论,如需转载原创文章请注明出处,谢谢!

博客历程 留言本 文章归档 网站地图 谷歌地图 浙ICP备13025236号-1
托管于阿里云&七牛云 已加入博客联盟 浙公网安备 33021202000610号
Copyright © 2010-2017 虫虫开源 All Rights Reserved.