mysql报11004_mysql udpate语句错误解决

今天执行一个sql 报错如下

Error : You can't specify target table 'p' for update in FROM clause

对着sql看来看去没看出有什么语法错误，上网搜索才知道 mysql执行更新sql where语句条件不运行引用表自身的子查询 so问题

The reason why this doesn't work is that MySQL doesn't allow you to reference the table that you are updating (cancome) within a subquery.

This can however be overcome by using a query instead of the table itself in the FROM, which has the effect of copying the requested table values instead of referencing the one that you are updating.

该问题真正执行的sql比较复杂，为了简化问题我对sql做里简化，但是问题本质不变和解决方法是一样的

UPDATE tb_purchase p set p.p_version = p.p_version + 1 where p.purchase_id in (

SELECT p2.purchase_id FROM tb_purchase p2 WHERE p2.purchase_id in ('0907105446NPH00171001','0907105446NPH00171004')

);

根据so的回复 sql修改如下解决

UPDATE tb_purchase p set p.p_version = p.p_version + 1 where p.purchase_id in (

SELECT purchase_id FROM (SELECT p2.purchase_id FROM tb_purchase p2 WHERE p2.purchase_id in ('0907105446NPH00171001','0907105446NPH00171004') ) temp_p

);

再执行一边sql 通过，问题解决