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:
I had no idea you could do this with an update statement and wanted to get it out there.
Categories: Immersion Events, SQL Server, SQLskills, T-SQL
SQL Server, SQLskills, T-SQL
This was really great. I never would have thought to do this. Thank you for sharing.