Mysql:Error Code : 1093 You can't specify target table 'TDevice' for update in 转

<div class="iteye-blog-content-contain" style="font-size: 14px"></div>

 MYSQL执行如下语句报错：

UPDATE `TDevice` SET `tracker_sim`='',`head_portrait`='',`mobile1`='',`mobile2`='',`mobile3`='',`nickname`='',`timezone`=0,`timezoneid`=31,`gps_interval`=NULL 

WHERE id IN ( SELECT a FROM ((SELECT id AS a FROM `TDevice` WHERE id NOT IN (SELECT t.did FROM `TDeviceUser` t LEFT JOIN `TDevice` d ON t.`did`=d.id) AND product_type=14)) AS temp)

 

报错信息如下：

　　错误代码： 1093
　　You can't specify target table 'TDevice' for update in FROM clause

意思是不能在同一语句中更新select出的同一张表元组的属性值

解决方法：将select出的结果通过中间表再select一遍即可。

UPDATE `TDevice` SET `tracker_sim`='',`head_portrait`='',`mobile1`='',`mobile2`='',`mobile3`='',`nickname`='',`timezone`=0,`timezoneid`=31,`gps_interval`=NULL 

WHERE id IN (SELECT id AS a FROM `TDevice` WHERE id NOT IN (SELECT t.did FROM `TDeviceUser` t LEFT JOIN `TDevice` d ON t.`did`=d.id) AND product_type=14)