<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)