如何从同一表中的另一条记录中更新一条记录？

Question

我在MySQL 5.1.70中内置了一个pricing表和一个products表。

新的定价表结构：

`priceid` int(11) NOT NULL AUTO_INCREMENT
`itemid` int(11) NOT NULL DEFAULT '0'
`class` enum('standard','wholesale') NOT NULL
`price` decimal(12,2) NOT NULL DEFAULT '0.00'
`owner` int(11) NOT NULL DEFAULT '0'

旧的价格表结构：

`priceid` int(11) NOT NULL AUTO_INCREMENT
`itemid` int(11) NOT NULL DEFAULT '0'
`price` decimal(12,2) NOT NULL DEFAULT '0.00'
`owner` int(11) NOT NULL DEFAULT '0'

新产品表结构：

`itemid` int(11) NOT NULL AUTO_INCREMENT
`title` varchar(255) NOT NULL DEFAULT ''
`msrp` decimal(12,2) NOT NULL DEFAULT '0.00'

旧产品表结构：

`itemid` int(11) NOT NULL AUTO_INCREMENT
`title` varchar(255) NOT NULL DEFAULT ''
`wholesale_price` decimal(12,2) NOT NULL DEFAULT '0.00'
`msrp` decimal(12,2) NOT NULL DEFAULT '0.00'

下面是新的products表中的一个示例行：

'12345', 'Toy Drum', '25.00'

以下是同一项目的新定价表中的两个示例行：

'123', '12345', 'wholesale', '10.00', '2'
'124', '12345', 'standard', '20.00', '2'

我有以下查询，我正在尝试重新处理上面的新表设置，因为旧的设置在products表中有wholesale_price：

UPDATE products, pricing, price_rules
SET pricing.price = IF(
    price_rules.markdown > 0,
    products.msrp - price_rules.markdown * products.msrp,
    products.wholesale_price + price_rules.markup * products.wholesale_price
) WHERE
    products.itemid = pricing.itemid AND
    pricing.owner = price_rules.owner;

复杂之处在于，批发价和标准价现在都在同一个itemid下的同一个表中，但class不同。

我如何让这个查询在新的表结构下(有效地)工作？

表中有大约200,000条记录。

Answer 1

首先，您需要一个真正有效的查询-您稍后会担心效率问题。

看看this SQLFiddle demo，这个例子展示了如何将来自同一个表的两行与‘批发’和‘标准’值配对。

下面是更新查询的示例another demo (该查询位于build schema选项卡的底部)，下面显示了对示例数据运行此更新后的结果。

UPDATE products, pricing new, pricing old, price_rules
SET new.price = IF(
    price_rules.markdown > 0,
    products.msrp - price_rules.markdown * products.msrp,
    products.wholesale_price + price_rules.markup * products.wholesale_price
) WHERE
    old.class = 'wholesale' AND
    new.class = 'standard' AND 
    old.itemid = new.itemid AND
    products.itemid = new.itemid AND
    new.owner = price_rules.owner;