从表中设置变量 [英] Set A Variable From A Table

问题描述

如果我想为表中的字段设置一个变量，我通常使用类似的东西

If I want to set a variable to a field in a table I normally use something like

SELECT @UserIdToUpdate = userId FROM #NewUsers

在这种情况下会有多个结果，我只想要第一个结果，所以我尝试了这个，但它失败并说无效语法顶部

In this case there will be multiple results and I just want the first one so I tried this but it fails and says invalid syntax top

SELECT @UserIdToUpdate = TOP 1 UserId FROM #NewUsers

如果是这种情况，我可以只使用没有顶部的第一个示例吗?我认为它只会记录第一条记录?我知道这似乎是一件奇怪的事情，但该命令处于循环状态，因此它将选择一条记录，对其进行处理，将其删除，然后选择下一条记录.

If this is this case can I just usethe first example without the top? I assume it will just take the first record? I know it seems like on odd thing to do but the command is in a loop so it will select a record, do something with it, delete it then select the next one.

推荐答案

SELECT @UserIdToUpdate = NULL
SELECT TOP 1 @UserIdToUpdate = userId FROM #NewUsers

需要第一条语句，因为如果第二条语句找到零行，则变量根本不会被赋值，而是保留其先前的值.

The first statement is needed because if the second finds zero rows, then the variable will not get assigned at all and will keep its prior value.

或者，

SELECT @UserIdToUpdate = (SELECT TOP 1 userId FROM #NewUsers)

即使找到零行，这也会起作用.

this will work even if zero rows are found.

这篇关于从表中设置变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！