mysql - 重复更新时插入...导致字段列表中的未知列，但该字段存在

Question

当我运行查询时，我收到错误“错误代码:‘字段列表’中未知列‘t2.FSVisitsToDate’，但我无法弄清楚我的查询错误在哪里。任何人都可以指出我做错了什么吗？

INSERT INTO CMCustomer
        (CustomerNumber,
            LastName,
            FirstName,
            Address,
            City,
            State,
            ZIPCode,
            PhoneNo,
            DriverLicenseNo,
            SocialSecNo,
            TaxExempt,
            ExternalRefNumber,
            AuxField,
            Comments,
            FSLevelNo,
            FSDateOpened,
            FSLastVisit,
            FSVisitsToDate,
            FSVisitsThisPeriod,
            FSPurchaseToDate,
            FSPurchaseThisPeriod,
            FSDiscountToDate,
            FSDiscountThisPeriod,
            FSPointsToDate,
            FSPointsThisPeriod,
            FSPromoPointsToDate,
            FSPromoPointsThisPeriod,
            LastUpdated,
            Employee)
SELECT t1.CustomerNumber,
            t1.LastName,
            t1.FirstName,
            t1.Address,
            t1.City,
            t1.State,
            t1.ZIPCode,
            t1.PhoneNo,
            t1.DriverLicenseNo,
            t1.SocialSecNo,
            t1.TaxExempt,
            t1.ExternalRefNumber,
            t1.AuxField,
            t1.Comments,
            t1.FSLevelNo,
            t1.FSDateOpened,
            t1.FSLastVisit,
            t1.FSVisitsToDate,
            t1.FSVisitsThisPeriod,
            t1.FSPurchaseToDate,
            t1.FSPurchaseThisPeriod,
            t1.FSDiscountToDate,
            t1.FSDiscountThisPeriod,
            t1.FSPointsToDate,
            t1.FSPointsThisPeriod,
            t1.FSPromoPointsToDate,
            t1.FSPromoPointsThisPeriod,
            t1.LastUpdated,
            t1.Employee
FROM cm01process t1
LEFT JOIN CMCustomer t2 ON t2.CustomerNumber = t1.CustomerNumber
ON DUPLICATE KEY UPDATE         
        t2.FSVisitsToDate = t2.FSVisitsToDate + t1.FSVisitsToDate,
        t2.FSVisitsThisPeriod = t2.FSVisitsThisPeriod + t1.FSVisitsThisPeriod,
        t2.FSPurchaseToDate = t2.FSPurchaseToDate + t1.FSPurchaseToDate,
        t2.FSPurchaseThisPeriod = t2.FSPurchaseThisPeriod + t1.FSPurchaseThisPeriod,
        t2.FSDiscountToDate = t2.FSDiscountToDate + t1.FSDiscountToDate,
        t2.FSDiscountThisPeriod = t2.FSDiscountThisPeriod + t1.FSDiscountThisPeriod,
        t2.FSPointsToDate = t2.FSPointsToDate + t1.FSPointsToDate,
        t2.FSPointsThisPeriod = t2.FSPointsThisPeriod + t1.FSPointsThisPeriod,
        t2.FSPromoPointsToDate = t2.FSPromoPointsToDate + t1.FSPromoPointsToDate,
        t2.FSPromoPointsThisPeriod = t2.FSPromoPointsThisPeriod + t1.FSPromoPointsThisPeriod;

我想要完成的是从我的商店之一获取文件并将其导入到我的数据库中。如果是新客户，我需要添加行，如果是重复客户，我需要更新字段(添加到用户的点)。

Answer 1

我不确定为什么这会起作用，所以我希望有人解释一下，以便我有更好的理解，但如果我像这样构造语句的“重复...”部分，显然查询会成功完成:

ON DUPLICATE KEY UPDATE         
        CMCustomer.FSVisitsToDate = CMCustomer.FSVisitsToDate + values(FSVisitsToDate),
        CMCustomer.FSVisitsThisPeriod = CMCustomer.FSVisitsThisPeriod + values(FSVisitsThisPeriod),
        CMCustomer.FSPurchaseToDate = CMCustomer.FSPurchaseToDate + values(FSPurchaseToDate),
        CMCustomer.FSPurchaseThisPeriod = CMCustomer.FSPurchaseThisPeriod + values(FSPurchaseThisPeriod),
        CMCustomer.FSDiscountToDate = CMCustomer.FSDiscountToDate + values(FSDiscountToDate),
        CMCustomer.FSDiscountThisPeriod = CMCustomer.FSDiscountThisPeriod + values(FSDiscountThisPeriod),
        CMCustomer.FSPointsToDate = CMCustomer.FSPointsToDate + values(FSPointsToDate),
        CMCustomer.FSPointsThisPeriod = CMCustomer.FSPointsThisPeriod + values(FSPointsThisPeriod),
        CMCustomer.FSPromoPointsToDate = CMCustomer.FSPromoPointsToDate + values(FSPromoPointsToDate),
        CMCustomer.FSPromoPointsThisPeriod = CMCustomer.FSPromoPointsThisPeriod + values(FSPromoPointsThisPeriod);

我认为您可以引用要插入的表，并且要从中插入的表必须使用 VALUES() 任何人都可以确认吗？