Home > Immersion Events, SQL Server, SQLskills, T-SQL > Using an UPDATE Statement to Update a Column and Set a Variable

Using an UPDATE Statement to Update a Column and Set a Variable

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
(id INT
,firstname VARCHAR(30)
,lastname VARCHAR(30)
,balance MONEY)

-- Insert a row into the test table

-- Declare variables for use below
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:

Balance Results

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

  1. Ron Koz
    April 19, 2012 at 13:38

    This was really great. I never would have thought to do this. Thank you for sharing.

  1. No trackbacks yet.

Leave a Reply

%d bloggers like this: