Using Variables in update Statements

Considerations for using variables in update statements.

  • You can assign variables in the set clause of an update statement, similarly to setting them in a select statement.

  • Before you use a variable in an update statement, you must declare the variable using declare, and initialize it with select, as shown in Example 5.

  • Variable assignment occurs for every qualified row in the update.

  • When a variable is referenced on the right side of an assignment in an update statement, the current value of the variable changes as each row is updated. The current value is the value of the variable just before the update of the current row. The following example shows how the current value changes as each row is updated.

    Suppose you have the following statement:
    declare @x int
select @x=0
update table1
    set C1=C1+@x, @x=@x+1
    where column2=xyz

    The value of C1 before the update begins is 1. This table shows how the current value of the @x variable changes after each update:

    Row

    Initial C1 value

    Initial @x value

    Calculations: C1+@x= updated C1

    Updated C1 value

    Calculations: @x+1= updated @x

    Updates value

    A

    1

    0

    1+0

    1

    0+1

    1

    B

    1

    1

    1+1

    2

    1+1

    2

    C

    2

    2

    2+2

    4

    2+1

    3

    D

    4

    3

    4+3

    7

    3+1

    4

  • When multiple variable assignments are given in the same update statement, the values assigned to the variables can depend on their order in the assignment list, but they might not always do so. For best results, do not rely on placement to determine the assigned values.

  • If multiple rows are returned and a non-aggregating assignment of a column to a variable occurs, then the final value of the variable is the last row processed; therefore, it might not be useful.

  • An update statement that assigns values to variables need not set the value of any qualified row.

  • If no rows qualify for the update, the variable is not assigned.

  • A variable that is assigned a value in the update statement cannot be referenced in subquery in that same update statement, regardless of where the subquery appears in that update statement.

  • A variable that is assigned a value in the update statement cannot be referenced in a where or having clause in that same update statement.

  • In an update driven by a join, a variable that is assigned a value in the right hand side of the update statement uses columns from the table that is not being updated. The result value depends on the join order chosen for the update and the number of rows that qualify from the joined table.

  • Updating a variable is not affected by a rollback of the update statement because the value of the updated variable is not stored on disk.

Parent topic: update