0

I am getting the error "Error Code: Unknown column 't2.FSVisitsToDate' in 'field list' when I run my query but I cannot figure out where my query is wrong. Can anyone point out what I did wrong?

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;

What I am trying to accomplish is to take a file from one of my stores and import it to my database. If it is a new customer I need the row added, and if it is a duplicate customer I need fields updated (points added to the user).

Aaron Martin
  • 397
  • 2
  • 6
  • 17
  • You need to show your query for us to have a chance at seeing what is wrong with your query. – D Mac Apr 19 '17 at 17:41
  • The query is already attached. it is right there in the code block, insert into.. from.. left join.. on duplicate key update query. – Aaron Martin Apr 19 '17 at 19:44
  • I'm no expert on this functionality of mysql, but shouldn't it be `ON DUPLICATE KEY UPDATE FSVisitsToDate = t2.FSVisitsToDate + t1.FSVisitsToDate, ...` Why are you trying to set `t2.FSVisitsToDate` here. (again no expert, but this feels wrong). – JNevill Apr 19 '17 at 20:03
  • t2 and t1 are aliases for the two tables. I am taking a select query from cm01process (t1) and inserting it into CMCustomer (t2). If the rows are new they are inserted into the table, if they have a duplicate key already in the table the query only updates the specified fields in (t2) with the values of (t2) plus (t1) from the corresponding row in (t1). – Aaron Martin Apr 19 '17 at 20:07

1 Answers1

0

I am not sure why this worked so I would love someone to explain so I have a better understanding but apparently the query completes successfully if I structure the "on duplicate..." part of the statement like so:

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);

I think that you can reference the table you are inserting into and the table you are inserting from must use VALUES() Can anyone confirm?

Aaron Martin
  • 397
  • 2
  • 6
  • 17