MySQL Sub-select in ON DUPLICATE KEY

Question

Is it possible to do a select in an IF statement in ON DUPLICATE KEY UPDATE?

Lets say I want to setup shipping rates by zip code, and in case of duplicate zip code entered, I want to apply the highest rate to apply.

My database has a table of shipping rates and a table of zip codes. The zip code table has a foreign key referencing the shipping rates, and the zip code which has a unique index.

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

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 returned more than one record". Being that there is only one matching record in the ShippingRates table, I don't understand how more than one record was returned. What is actually happening is that in the sub-select, ShippingRateID is always referring to the ShippingRates table, and the ShippingRateID of the insert is not being used. So without using LIMIT, all the shipping rates are being returned. If I do use LIMIT 1, then I'll always get the first rate, which is wrong.

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

Answer 1

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
    )