MySQL(SQL) 1093 error-Error Code: 1093. You can’t specify target table ‘XXXXXXX’ for update in FROM clause

資料來源: https://blog.csdn.net/u010657094/article/details/64439486

因為修改和查詢是在同一個表

錯誤語法:

UPDATE user_car_group_detailed SET state=1 WHERE user_car_group_id IN (SELECT user_car_group_id FROM user_car_group_detailed WHERE card_id=2006);
#1093 - You can't specify target table 'user_car_group_detailed' for update in FROM clause

修正方法:(先將查詢變成一個虛擬表，在使用即可)

UPDATE user_car_group_detailed SET state=1 WHERE user_car_group_id IN (SELECT user_car_group_id FROM (SELECT user_car_group_id FROM user_car_group_detailed WHERE card_id=2006) AS tmp);