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”.
