mysql - 根据第二个表中的计数更新连接字段

Question

我需要根据连接表中的连接和计数更新两个字段(Trans.PtPlanId 和 Trans.PtLot)。连接有效，但更新无效。

此连接返回正确的行:

SELECT
    Plan.Prp_PartNum AS PartNum,
    Count(Plan.Prp_PartNum) AS PartNum_Count,
    Plan.Prp_ProdPlanId,
    Plan.Prp_Lot,
    Trans.PtPlanId,
    Trans.PtLot
FROM
    prod_plan AS Plan
LEFT JOIN product_trans AS Trans ON Trans.PtPartNum = Plan.Prp_PartNum
WHERE
    IFNULL(plan.Prp_Closed,0) = 0 AND
    Trans.PtRole = 'XBO' AND
    Trans.PtPosted IS NULL
GROUP BY
    Plan.Prp_PartNum,
    Plan.Prp_ProdPlanId,
    Plan.Prp_Lot
HAVING
    PartNum_Count = 1
ORDER BY
    PartNum ASC

以下更新会产生 [Err] 1064:

UPDATE product_trans AS Trans
LEFT JOIN (
    SELECT
        Plan.Prp_PartNum AS PartNum,
        Count(Plan.Prp_PartNum) AS PartNum_Count,
        Plan.Prp_ProdPlanId,
        Plan.Prp_Lot
    FROM
        prod_plan
    WHERE
        IFNULL(plan.Prp_Closed, 0) = 0
    GROUP BY
        Plan.Prp_PartNum,
        Plan.Prp_ProdPlanId,
        Plan.Prp_Lot
    HAVING
        Plan.PartNum_Count = 1
    ORDER BY
        Plan.Prp_PartNum ASC
) AS Plan 

ON Trans.PtPartNum AS Trans = Plan.Prp_PartNum

SET Trans.PtPlanId = Plan.Prp_ProdPlanId,
 Trans.PtLot = Plan.Prp_Lot

WHERE
Trans.PtRole = 'XBO' AND
Trans.PtPosted IS NULL

我哪里出错了？

Answer 1

根据发布的评论更新:

UPDATE product_trans AS Trans
LEFT JOIN (
    SELECT
        prod_plan.Prp_PartNum AS PartNum,
        Count(prod_plan.Prp_PartNum) AS PartNum_Count,
        prod_plan.Prp_ProdPlanId AS Plan_Id,
        prod_plan.Prp_Lot AS Lot
    FROM
        prod_plan
    WHERE
        IFNULL(prod_plan.Prp_Closed, 0) = 0
    GROUP BY
        PartNum,
        Plan_Id,
        Lot
    HAVING
        PartNum_Count = 1
    ORDER BY
        Prp_PartNum ASC
) AS Plan 

ON Trans.PtPartNum = Plan.PartNum

SET Trans.PtPlanId = Plan.Plan_Id,
 Trans.PtLot = Plan.Lot

WHERE
Trans.PtRole = 'XBO' AND
Trans.PtPosted IS NULL