SQL 2005/8 Quirky 更新“黑魔法" [英] SQL 2005/8 Quirky update "black arts"

问题描述

大家好，SQL Server Blackbelt 高手们.

Hi all SQL Server Blackbelt masters out there.

我有一个简单的问题需要解决.我正在尝试对桌子进行一些古怪的更新.表的具体结构并不重要，我的问题基本上可以归结为以下一个:

I have a simple question that I need to solve. I am trying to do some quirky update on a table. Specific structure of the table is not important and my problem can essentially be reduced to the following one:

update SalesTotal
set @total = total = @total + sales,
@flag = flag = case when @flag = 1 then 0 
                    when @total > x then 1
                    else 0 end

问题是我能否相信@total 变量会有一个新值，即.将在第二个集合部分之前执行，或者它会包含一个旧"值(来自先前的赋值)或者它是未定义的.我已经研究这个问题有一段时间了，但找不到解决方案，这对我的更新非常重要(我想一次性更新一张表).我要更新的值之一取决于另一个值(在同一行中)，因此它在单个更新中工作的唯一方法是它是否有效.

The question is CAN I TRUST that @total variable will have a new value ie. will execute before the second set part, or will it contain an "old" value (from previous assignment) or it is undefined. I am pursuing this problem for some time now, and can't find a solution and it is really important for my update (I want to update a table in one go). One of the values I want to update depends on the value of the other (in the same row) so the only way for it to work in a single update is if it works.

希望你能帮到我

推荐答案

不，您不能对命令式执行顺序进行任何假设.绝对不能保证表中更新位置的顺序，绝对不能保证 @variable 为每个表 SET 更新一次，并且更新很可能在 万圣节保护.

No, you cannot make any assumption of imperative order of execution. There is absolutely no guarantee about the order of the updates tacking place in the table, there is absolutely no guarantee that the @variable is updated once for each table SET, and the update may well run with a spool step for Halloween protection.

这篇关于SQL 2005/8 Quirky 更新“黑魔法"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！