Is it possible to do a select in an IF statement in ON DUPLICATE KEY UPDATE?
是否可以在ON DUPLICATE KEY UPDATE中的IF语句中进行选择?
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:
当存在重复密钥时,我想将运费率ID设置为更高的费率。像这样的东西:
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))
INSERT INTO ZipCodes(ShippingRateID,Zip)VALUES(11,'13754')ON DUPLICATE KEY UPDATE ShippingRateID = IF((选择率来自ShippingRates sr WHERE sr.ShippingRateID = ShippingRateID)> [当前费率],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.
当我尝试执行此查询时,MySQL告诉我“Subquery返回了多条记录”。由于ShippingRates表中只有一个匹配的记录,我不明白如何返回多个记录。实际发生的是,在子选择中,ShippingRateID始终引用ShippingRates表,并且未使用插入的ShippingRateID。因此,如果不使用LIMIT,则会返回所有运费。如果我使用LIMIT 1,那么我将始终获得第一个速率,这是错误的。
How do I tell the sub-select to use ShippingRateID from the insert?
如何告诉子选择使用插入中的ShippingRateID?
1 个解决方案
#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
)
#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
)