MySql 更新 : Can't specify target table for update in FROM clause

Question

我正在尝试对满足特定条件的值执行更新操作。我的表 CAPD、CAMP、CAD。但是我得到了错误

Error Code: 1093. You can't specify target table 'CAPD' for update in FROM clause

UPDATE CAPD SET CAPD.Is_Active = 1
WHERE CAPD.Per_Id IN (
    SELECT CAMP.Id

    FROM CAMP 

    INNER JOIN CAPD ON (
        CAPD.Per_Id = CAMP.Id 
        AND CAPD.Is_Active = 0 
    ) 
    INNER JOIN CAD ON (
        CAD.Id = CAPD.Deploy_Id 
        AND BINARY CAD.Access_Id = "486579446F6E277-4436F6E7665727449742E2E4C-4F4C203A5020584F586F"
    ) 

    WHERE CAMP.Serial = "ABC1230071"
)

Answer 1

You cannot use the target table which you are updating inside the subquery. You need to use the JOIN in case you want to use it -- First answer by @Rahul Tripathi

你必须尝试这个查询然后你需要设置SET SQL_SAFE_UPDATES

SET SQL_SAFE_UPDATES=0;

UPDATE CAPD 
INNER JOIN CAD ON ( CAD.Id = CAPD.Deploy_Id) 

SET CAPD.Is_Active = 1

WHERE CAPD.Per_Id IN (
 SELECT CAMP.Id 
    FROM CAMP 
    WHERE CAMP.Serial = "ABC1230071"
)

AND  BINARY CAD.Access_Id = "486579446F6E277-4436F6E7665727449742E2E4C-4F4C203A5020584F586F";

SET SQL_SAFE_UPDATES=1;