There are lot of scenarios we encounter where we need to know the value of something that was just updated – a row id, a field…the easiest and most common way to do this is to run a select statement soon after the update to get the value, but there is a simpler alternative.

 Consider below (tried on Adventureworks)

DECLARE @PRICEINCREASE INT, @NEWUNITPRICE MONEY

SELECT @PRICEINCREASE = 10

BEGIN TRANSACTION

UPDATE Sales.SalesOrderDetail SET 
    UnitPrice = UnitPrice + @PRICEINCREASE  
     WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1 AND ProductID = 776

COMMIT TRANSACTION

SELECT @NEWUNITPRICE = UnitPrice 
    FROM Sales.SalesOrderDetail   
    WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1 AND ProductID = 776

 The same can be easily accomplished in one statement as below

DECLARE @PRICEINCREASE INT, @NEWUNITPRICE MONEY

SELECT @PRICEINCREASE = 10

 BEGIN TRANSACTION

 UPDATE Sales.SalesOrderDetail SET 
    @NEWUNITPRICE = UnitPrice = UnitPrice + @PRICEINCREASE 
    WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1 AND ProductID = 776

COMMIT TRANSACTION

 The first statement took 2 seconds and the second one, less than a second.

 To be aware that only one value is returnable this way, so if you are updating more than one row, there is no real guarantee what will be returned, although in most cases it appears to be the value of the last row that was updated – in order of clustered index.

But it is a neat trick to master when you need one value out of the update and need to use two statements to do it. As of now there is no real name for the = = usage.