Have Geek Brain

MySQL Sub-select in ON DUPLICATE KEY

Is it possible to make a selection in an IF statement in ON DUPLICATE KEY UPDATE?

Suppose I want to adjust the shipping rate by zip code, and if I enter a double zip code, I want to apply the highest rate to apply.

My database has a shipping rate table and a postcode table. The zip code table has a foreign key that indicates the shipping rate and a postcode with a unique postcode.

When there is a duplicate, I want to set the shipping rate ID to a higher rate. Something like that:

INSERT INTO ZipCodes (ShippingRateID, Zip) VALUES (11, '13754') ON DUPLICATE KEY UPDATE ShippingRateID = IF((SELECT Rate FROM ShippingRates sr WHERE sr.ShippingRateID = ShippingRateID) > [current rate], ShippingRateID, VALUES(ShippingRateID))

When I try to execute this query, MySQL tells me, "Subquery returns more than one record." Since there is only one matching record in the ShippingRates table, I don't understand how more than one record was returned. What actually happens is that the ShippingRateID subset always refers to the ShippingRates table, and the ShippingRateID value of the insert is not used. Therefore, without using LIMIT, all shipping rates are refunded. If I use LIMIT 1 then I will always get the first bid which is not correct.

How do I tell the sub-select to use the ShippingRateID from the insert?

+1


a source to share


1 answer 


INSERT INTO ZipCodes
(ShippingRateID, Zip)
VALUES (11, '13754')
ON DUPLICATE KEY
UPDATE ShippingRateID = 
    (SELECT IF(Rate > [current rate], ShippingRateID, VALUES(ShippingRateID))
        FROM (
            SELECT ShippingRateID AS AltID, Rate
            FROM ShippingRates
        ) AS sr
        WHERE sr.AltID = ShippingRateID
        LIMIT 1
    )


+2


a source






More articles:


All Articles