skreebydba

I am a day and a half into the SQLskills Internals Immersion Event and I expect to have a lot more to post once I've recovered. Kimberly Tripp (blog | twitter) covered a feature of the UPDATE statement that I was unaware of. It allows you to update a column and set a variable with the updated value in a single statement. Here is an example:

-- Create a test table
CREATE TABLE UpdateTest
(id INT
,firstname VARCHAR(30)
,lastname VARCHAR(30)
,balance MONEY)

-- Insert a row into the test table
INSERT INTO UpdateTest
VALUES
(1
,'Skreeby'
,'Looby'
,1234.56)

-- Declare variables for use below
DECLARE @balance MONEY
DECLARE @debit MONEY
SET @debit = 500.00

-- Update table, updating the balance column equal to balance - @debit and setting
-- the @balance at the same time
-- NOTE: If more than one row is updated, your variable will contain the column value 
-- from the last row updated

UPDATE UpdateTest
SET @balance = balance = balance - @debit
WHERE id = 1

-- Select the variable and table
SELECT @balance AS [Balance]

SELECT * FROM UpdateTest

Here are the results:

 
I had no idea you could do this with an update statement and wanted to get it out there.