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