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
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:

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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: