Assigning variables in the set clause

You can assign variables in the set clause of an update statement, in the same way you can assign them in a select statement. Using variables with update reduces lock contention and CPU consumption that can occur when extra select statements are used with update.

This example uses a declared variable to update the titles table:

declare @price money
select @price = 0
update titles
    set total_sales = total_sales + 1,
    @price = price
    where title_id = "BU1032"
select @price, total_sales
    from titles
    where title_id = "BU1032"

                          total_sales
 ------------------------ -----------
                    19.99        4096
 
(1 row affected)