Set a variable from a table

Question

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

SELECT @UserIdToUpdate = userId FROM #NewUsers 

In this case, there will be several results, and I just want the first one, so I tried this, but it fails and talks about the invalid syntax top

 SELECT @UserIdToUpdate = TOP 1 UserId FROM #NewUsers 

If so, can I just use the first example without a vertex? I guess he just takes the first record? I know that it seems strange, but the team is in a loop, so it will select the record, do something with it, delete it and select the next one.

Answer 1

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

The first operator is necessary because if the second finds zero rows, then the variable will not be assigned at all and will retain its previous value.

As an alternative,

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

this will work even if zero rows are found.

Answer 2

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

But I believe that your first request will work if you delete this line. And SQL Server doesn’t have to read all the rows, but in fact it only has to select the first one (in random order)

Answer 3

 SELECT TOP 1 @UserIdToUpdate = UserId FROM #NewUsers 

Answer 4

This should work

 SELECT @UserIdToUpdate = userId FROM #NewUsers LIMIT 1