Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL, updating more than one variable in a single select

People also ask

How do you update multiple variables in SQL?

To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.

Can we set two values in update query?

We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.

How do I assign multiple values to a single variable in SQL?

Assigning multiple values to multiple variables If you have to populate multiple variables, instead of using separate SET statements each time consider using SELECT for populating all variables in a single statement. This can be used for populating variables directly or by selecting values from database.


Something like this:

select @var1 = avg(someColumn), @var2 = avg(otherColumn) 
from theTable

You can use SELECT assignment to assign multiple variables. This code generates a single row of constants and assigns each to a variable.

SELECT
  @var1 = 1,
  @var2 = 'Zeus'

You can even query tables and do assignment that way:

SELECT
  @var1 = c.Column1,
  @var2 = c.Column2,
FROM
  Customers c
WHERE c.CustomerID = @CustomerID

Beware: This code operates like a while loop.

  • If there are multiple rows, each row will be assigned to the variables and the last row will be the one left in there. If you didn't specify an ordering, you gave up control over which row will be the last row.
  • If there are no rows, the variables will not be assigned to at all. The variables will not be set to null - they will remain unchanged. This is a key problem if the assignment is done in a loop (typically resulting in an infinite loop as the variables never change).

Prefer using SET assignment over SELECT assignment. Only use SELECT assignment when considering both scenarios above.


how about

SELECT  @variableOne = avg(someColumn),  @variableTwo = avg(otherColumn) from tblTable 

it works for me just fine.