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

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

如果你习惯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

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

icon_wink.gif icon_neutral.gif icon_mad.gif icon_twisted.gif icon_smile.gif icon_eek.gif icon_sad.gif icon_rolleyes.gif icon_razz.gif icon_redface.gif icon_surprised.gif icon_mrgreen.gif icon_lol.gif icon_idea.gif icon_biggrin.gif icon_evil.gif icon_cry.gif icon_cool.gif icon_arrow.gif icon_confused.gif icon_question.gif icon_exclaim.gif 

一键脚本 SSH攻击 IP查询 博客历程 留言本 文章归档 网站地图 谷歌地图
托管于阿里云&七牛云. 已加入博客联盟. 浙ICP备13025236号.
Copyright © 2010-2016 虫虫开源 All Rights Reserved.