Chapter 7: Adding, Changing, and Deleting Data

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 in conjunction 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)

For details on assigning variables in an update statement, see the Reference Manual. For more information on declared variables, see “Local variables”.

View this book as PDF