高效更新SQL，使用一条SQL语句更新多行，避免循环

Question

我试图避免在我正在处理的项目中的循环中访问数据库。对SQL不太好，我不确定解决这个问题的最好方法。

我正在更新具有多个库存位置/拣配位置的销售过程中的库存水平数据库。

因此，这就是我正在做的。

通过产品ID循环，然后通过每个产品的选秀位置循环和更新数量，因为它去，如：

当然这个工作，但它是开放的每一个股票的新的数据库连接位置，为每个项目。

那么我如何将这个工作变成一个更新声明？

http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

这种联系让我非常接近我所后，我想，但我不是100％确定如何动态地构建SQL语句，以及如何将两个条件添加到CASE。

我需要建立一个SQL语句是这样的：

UPDATE stockLevels 
SET stockLevel= CASE id 
    WHEN '"& wProductId &"' AND stockLocation='"& thisLocation &"' THEN `stockLevel` - '" & thisQty & "' 
    WHEN '"& NEXTwProductId &"' AND stockLocation='"& NEXTthisLocation &"' THEN `stockLevel` - '" & NEXTthisQty & "' 
END 

但是，这不是正确的地方我加入的第二个参数的CASE！

我像往常一样使用MySQL和VB.NET，非常感谢。

Answer 1

您的CASE表达式在语法上根本不正确。

CASE表达式有两种，几乎相同，但语法略有不同。

一个人的

CASE expr 
    WHEN value1 THEN result1 
    WHEN value2 THEN result2 
    ... 
    ELSE result_else 
END 

形式向对方这个样子的：

CASE 
    WHEN condition1 THEN result1 
    WHEN condition2 THEN result2 
    ... 
    ELSE result_else 
END 

你基本上试图将这两种混合的CASE。

你可能只需要使用第二个（也称为搜索CASE，如果我没有弄错）：

... 
CASE 
    WHEN id = '"& wProductId &"' AND stockLocation='"& thisLocation &"' THEN ... 
    WHEN id = '"& NEXTwProductId &"' AND stockLocation='"& NEXTthisLocation &"' THEN ... 
... 

需要注意的是，如果没有匹配和CASE没有ELSE部分，其结果将是NULL，所以一定要确保你已经覆盖了所有的情况下，否则使用ELSE部分是这样的：

ELSE `stocklevel` 

即CASE将评估为正在更新的列的原始值，最终不会对其进行更新。

Answer 2

在您的示例中，在set子句中使用case语句并不理想，原因很多。

• 没有where子句，以帮助数据库执行查询有效
• 查询的大量更新的的尺寸过大（考虑更新1000行这样）
• 要手动实施加入 - 数据库几乎可以肯定比你更有效地做到这一点。
• 调试这样的查询也很困难。

相反，你应该先衡量一个更新的性能在时间的方法，看看你是否真的需要做出改进。

如果需要提高性能，那么我会建议一种方法，首先将更新插入到临时表中。一种合适的表将具有以下列：

wProductID, stockLocation, newStockLevel 

这些更新可以批量使用以下的MySQL语法插入：

INSERT INTO temp_stock_updates 
    (wProductID, stockLocation, newStockLevel) 
VALUES 
    (?,?,?), (?,?,?), (?,?,?), ... 

然后单个更新运行来更新主表。此查询看起来是这样的：

UPDATE stockLevels s 
    JOIN temp_stock_updates u USING (wProductID, stockLocation) 
SET 
    s.stockLevel = u.newStockLevel