我需要基于联接和联接表中的计数来更新两个字段(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


我要去哪里错了?

最佳答案

根据发布的评论更新:

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

关于mysql - 根据第二张表中的计数更新联接上的字段,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35386075/

10-12 12:30
查看更多