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

如果你习惯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都是同一个表。