MySql- update :无法指定目标表以便在FROM子句中更新

Question

我试图对符合某些标准的值执行更新操作。我的桌子CAPD, CAMP, CAD。但我得到了错误

错误代码: 1093。不能指定目标表“CAPD”以便在FROM子句中更新

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

不能使用子查询中正在更新的目标表。你需要使用连接，以防你想要使用它-- @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;

Answer 2

不能使用子查询中正在更新的目标表。您需要使用JOIN，以防您想使用它。就像这样：

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 CAD.Access_Id = "486579446F6E277-4436F6E7665727449742E2E4C-4F4C203A5020584F586F"
and CAPD.Is_Active = 0