How do you properly construct an IF EXIST UPDATE ELSE INSERT?

48 views Asked by At

I have a tables containing the dollars a customer spends as well as points they earn. I have a master table that I intend to accumulate the points/dollars and redistribute them to the stores so that points are shared accross all stores. The query to determine the difference of the master table (CMCustomer) and the store tables (cm01, cm03, etc...) are as follows, a separate query for each store.

use customer;
truncate cm01process;
INSERT INTO cm01process
select cm01.CustomerNumber,
cm01.LastName,
cm01.FirstName,
cm01.Address,
cm01.City,
cm01.State,
cm01.ZIPCode,
cm01.PhoneNo,
cm01.DriverLicenseNo,
cm01.SocialSecNo,
cm01.TaxExempt,
cm01.ExternalRefNumber,
cm01.AuxField,
cm01.Comments,
cm01.FSLevelNo,
cm01.FSDateOpened,
cm01.FSLastVisit,
IFNULL(IF( (cm01.FSVisitsToDate - CMCustomer.FSVisitsToDate) < 0,  0, (cm01.FSVisitsToDate - CMCustomer.FSVisitsToDate) ), 0 ) AS FSVisitsToDate,
IFNULL(IF( (cm01.FSVisitsThisPeriod - CMCustomer.FSVisitsThisPeriod) < 0,  0, (cm01.FSVisitsThisPeriod - CMCustomer.FSVisitsThisPeriod) ), 0 ) AS FSVisitsThisPeriod,
IFNULL(IF( (cm01.FSPurchaseToDate - CMCustomer.FSPurchaseToDate) < 0,  0, (cm01.FSPurchaseToDate - CMCustomer.FSPurchaseToDate) ), 0 ) AS FSPurchaseToDate,
IFNULL(IF( (cm01.FSPurchaseThisPeriod - CMCustomer.FSPurchaseThisPeriod) < 0,  0, (cm01.FSPurchaseThisPeriod - CMCustomer.FSPurchaseThisPeriod) ), 0 ) AS FSPurchaseThisPeriod,
IFNULL(IF( (cm01.FSDiscountToDate - CMCustomer.FSDiscountToDate) < 0,  0, (cm01.FSDiscountToDate - CMCustomer.FSDiscountToDate) ), 0 ) AS FSDiscountToDate,
IFNULL(IF( (cm01.FSDiscountThisPeriod - CMCustomer.FSDiscountThisPeriod) < 0,  0, (cm01.FSDiscountThisPeriod - CMCustomer.FSDiscountThisPeriod) ), 0 ) AS FSDiscountThisPeriod,
IFNULL(IF( (cm01.FSPointsToDate - CMCustomer.FSPointsToDate) < 0,  0, (cm01.FSPointsToDate - CMCustomer.FSPointsToDate) ), 0 ) AS FSPointsToDate,
IFNULL(IF( (cm01.FSPointsThisPeriod - CMCustomer.FSPointsThisPeriod) < 0,  0, (cm01.FSPointsThisPeriod - CMCustomer.FSPointsThisPeriod) ), 0 ) AS FSPointsThisPeriod,
IFNULL(IF( (cm01.FSPromoPointsToDate - CMCustomer.FSPromoPointsToDate) < 0,  0, (cm01.FSPromoPointsToDate - CMCustomer.FSPromoPointsToDate) ), 0 ) AS FSPromoPointsToDate,
IFNULL(IF( (cm01.FSPromoPointsThisPeriod - CMCustomer.FSPromoPointsThisPeriod) < 0,  0, (cm01.FSPromoPointsThisPeriod - CMCustomer.FSPromoPointsThisPeriod) ), 0 ) AS FSPromoPointsThisPeriod,
cm01.LastUpdated
from cm01
left join CMCustomer on cm01.CustomerNumber = CMCustomer.CustomerNumber;

This query works fine. The next query is where I begin to have problems.

-EDIT-

I have updated the second query. It is not producing errors and appears to do mostly what I want however it is not adding properly. In the query below it should insert into the new table (processData) the fields from a selection. On duplicates it is supposed to update the fields specified and on new rows just add them. It appears to work ok except when the UPDATE is performed it returns all zeroes in those columns. Example: CMCustomer has 55 points, cm01process had 0 points. Instead of adding the two points together and giving me 55 it is returning 0. Any ideas what I have wrong here?

use customer;
INSERT INTO processData (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)
SELECT cm01process.CustomerNumber,
    cm01process.LastName,
    cm01process.FirstName,
    cm01process.Address,
    cm01process.City,
    cm01process.State,
    cm01process.ZIPCode,
    cm01process.PhoneNo,
    cm01process.DriverLicenseNo,
    cm01process.SocialSecNo,
    cm01process.TaxExempt,
    cm01process.ExternalRefNumber,
    cm01process.AuxField,
    cm01process.Comments,
    cm01process.FSLevelNo,
    cm01process.FSDateOpened,
    cm01process.FSLastVisit,
    cm01process.FSVisitsToDate,
    cm01process.FSVisitsThisPeriod,
    cm01process.FSPurchaseToDate,
    cm01process.FSPurchaseThisPeriod,
    cm01process.FSDiscountToDate,
    cm01process.FSDiscountThisPeriod,
    cm01process.FSPointsToDate,
    cm01process.FSPointsThisPeriod,
    cm01process.FSPromoPointsToDate,
    cm01process.FSPromoPointsThisPeriod,
    cm01process.LastUpdated
FROM cm01process
LEFT JOIN CMCustomer ON cm01process.CustomerNumber = CMCustomer.CustomerNumber
ON DUPLICATE KEY UPDATE     
        processData.FSVisitsToDate = CMCustomer.FSVisitsToDate + cm01process.FSVisitsToDate,
        processData.FSVisitsThisPeriod = CMCustomer.FSVisitsThisPeriod + cm01process.FSVisitsThisPeriod,
        processData.FSPurchaseToDate = CMCustomer.FSPurchaseToDate + cm01process.FSPurchaseToDate,
        processData.FSPurchaseThisPeriod = CMCustomer.FSPurchaseThisPeriod + cm01process.FSPurchaseThisPeriod,
        processData.FSDiscountToDate = CMCustomer.FSDiscountToDate + cm01process.FSDiscountToDate,
        processData.FSDiscountThisPeriod = CMCustomer.FSDiscountThisPeriod + cm01process.FSDiscountThisPeriod,
        processData.FSPointsToDate = CMCustomer.FSPointsToDate + cm01process.FSPointsToDate,
        processData.FSPointsThisPeriod = CMCustomer.FSPointsThisPeriod + cm01process.FSPointsThisPeriod,
        processData.FSPromoPointsToDate = CMCustomer.FSPromoPointsToDate + cm01process.FSPromoPointsToDate,
        processData.FSPromoPointsThisPeriod = CMCustomer.FSPromoPointsThisPeriod + cm01process.FSPromoPointsThisPeriod
0

There are 0 answers