时间戳

首页 > 解决方案 > 关于存储过程更新行并删除行总值的问题

sql - 关于存储过程更新行并删除行总值的问题

问题描述

我想从Transactions表中删除行（该表有一个外键Customers），然后更新Customers.StartingBalance以反映已删除金额的总和。

所以我创建了一个存储过程。这是我到目前为止所拥有的。

SET NOCOUNT ON;

DECLARE @CustomerBalances TABLE
(
    CustomerId INT,
    Amount BIGINT
);

-- Note: Caller has already begun a transaction

DELETE Transactions WITH (TABLOCK)
OUTPUT deleted.CustomerId, deleted.TotalAmount INTO @CustomerBalances
WHERE [TimeStamp] < @ArchiveDateTime;
IF EXISTS (SELECT 1 FROM @CustomerBalances)
BEGIN
    UPDATE Customers  WITH (TABLOCK)
    SET StartingBalance = StartingBalance +
        (SELECT SUM(Amount) FROM @CustomerBalances cb WHERE Id = cb.CustomerId)
END;
DELETE FROM @CustomerBalances

由于 SQL 不是我的核心能力，我试图更好地理解这个查询。UPDATE特别是，我对上面的陈述有一些疑问。

  1. 这将全部更新Customers，因为我没有WHERE条款，对吧？
  2. 这可以正确处理客户在表中有多个匹配行的情况@CustomerBalances，对吗？
  3. 这里需要这个EXISTS子句吗？
  4. 将SUM()返回 0 或者NULL如果没有匹配的行？
  5. 如果我没有最后的DELETE陈述，一切都会得到清理吗？
  6. 在我执行此操作时，不要对Transactionsor表进行任何更改，这一点至关重要。Customers我的使用在TABLOCK这里有意义吗？
  7. 关于我正在采取的整体方法有什么建议吗？

标签： sqlsql-servertsql

解决方案

  1. 这将更新所有客户，因为我没有 WHERE 子句，对吧？

是的。考虑添加 WHERE 子句，例如：

WHERE Id IN (SELECT DISTINCT CustomerId FROM @CustomerBalances)

这可以防止更新未更改的余额。

  1. 这可以正确处理客户在@CustomerBalances 表中具有多个匹配行的情况，对吗？

是的。因为您使用 SUM() 来聚合它们。

  1. 这里需要 EXISTS 子句吗？

它是推荐的，而不是必不可少的。最好只在记录存档后尝试更新余额。

  1. 如果没有匹配的行，SUM() 会返回 0 还是 NULL？

是的，这是一个错误，它会导致未归档交易的客户的余额设置为 NULL（如果不允许 NULL，则错误）。这将通过添加上面提到的 WHERE 子句来解决。如果您出于某种原因试图避免 WHERE，则可以使用 COALESCE(SUM(Amount),0.00) 修复它

  1. 如果我没有最后的 DELETE 语句，一切都会被清理吗？

是的。当过程完成时，表变量将自动超出范围，因此不需要 DELETE，就此片段显示而言。

  1. 在我执行此操作时，不要对 Transactions 或 Customers 表进行任何更改，这一点至关重要。我在这里使用 TABLOCK 有意义吗？

是的，但您还应该指定 HOLDLOCK 以保持锁定直到事务完成。

  1. 关于我正在采取的整体方法有什么建议吗？

见上文，但总的来说它看起来是合理的。

推荐阅读