TSQL OUTPUT Clause With UPDATE Statements
Earlier in the week I added a blog entry on using the OUTPUT Clause on INSERT Statements, today I will expand it by exploring the use of the OUTPUT clause with UPDATE statements.
Generally when you do an UPDATE statement you only get a row count returned, you don’t generally get a result set showing what was changed. With the OUTPUT clause we can have an UPDATE statement return the before and after of the rows being updated. Lets take a look at some sample code to do this.
First we create a sample database and table to work with.
USE Master;
GO
CREATE DATABASE [QueryTraining];
GO
USE [QueryTraining];
GO
CREATE TABLE [dbo].[Inventory](
[InventoryID] [INTEGER] IDENTITY(-2147483647,1) PRIMARY KEY,
[Category] [VARCHAR](200),
[ItemSKU] [VARCHAR](200),
[Description] [VARCHAR](4000),
[QuantityOnHand] [INT],
[UnitCost] DECIMAL(10,2),
[UnitRetail] DECIMAL(10,2),
[Archived] BIT
);
INSERT INTO dbo.Inventory (Category, ItemSKU, Description, QuantityOnHand,
UnitCost, UnitRetail, Archived)
VALUES
('SLEEPINGBAG', 'SB001-LG-RED', 'Sleeping Bag - Large - RED', 5, 5.75, 19.99, 0),
('SLEEPINGBAG', 'SB001-LG-GRN', 'Sleeping Bag - Large - GREEN', 13, 5.75, 14.99, 1),
('SLEEPINGBAG', 'SB001-LG-BLK', 'Sleeping Bag - Large - BLACK', 1, 5.75, 19.99, 0),
('TENT', 'TT001-10-CMO', 'Tent - 10 Man - CAMO', 5, 10.00, 99.95, 0),
('AXE', 'AX013-HT-BLK', 'Axe - Hatchet - Black', 35, 4.99, 24.95, 0),
('FOOD', 'MR005-1S-SPG', 'MRE - 1 Serve - Spaghetti', 5, 0.50, 7.99, 0),
('FOOD', 'MR006-1A-PIZ', 'Pizza', 1, NULL, 9.95, 0),
('FOOD', 'MR009-212-ICE', 'MRE - Ice Cream - Cookies and Cream', 1, NULL, 11.95, 0),
('FOOD', 'MR009-213-ICE', 'MRE - Ice Cream - Chocolate Chip', 1, NULL, 11.95, 0);
First lets start with a basic update statement without using the OUTPUT clause. This update statement is just renaming the item with a SKU of MR009-213-ICE to have a description of ‘MRE – Ice Cream – Double Chocolate Chip’.
UPDATE dbo.Inventory SET [Description] = 'MRE - Ice Cream - Double Chocolate Chip' WHERE [ItemSKU] = 'MR009-213-ICE';
Which gives us the following output, which only shows us the number of rows affected by the UPDATE statement.
But what if you want to know the details associated with the before and after state of the rows being updated.
This time we are going to update the double chocolate chip to triple chocolate chip using the output statement.
UPDATE dbo.Inventory SET [Description] = 'MRE - Ice Cream - Triple Chocolate Chip' OUTPUT [deleted].*, [inserted].* WHERE [ItemSKU] = 'MR009-213-ICE';
The output above although you can’t see it all as it scrolls off the right, includes the exact rows updated, with the before and the after state of every column.
How about if we only want to see the before and after of the description column, we could try something like this, here we are updating the product to be Strawberry ice cream:
UPDATE dbo.Inventory SET [Description] = 'MRE - Ice Cream - Strawberry' OUTPUT [deleted].Description, [inserted].Description WHERE [ItemSKU] = 'MR009-213-ICE';
When run the query produces the following output:
We can see that the ice cream was previously Triple Chocolate Chip, and that this item has been replaced with Strawberry ice cream.
Updating Multiple Rows
Lets now take a look at a bulk update, all products in the inventory table will have their retail price updated to be 10% more than they currently are. The SQL might look like this:
UPDATE dbo.Inventory SET [UnitRetail] = [UnitRetail] * 1.10 OUTPUT [deleted].InventoryID, [deleted].UnitRetail AS PriceBefore, [inserted].UnitRetail AS PriceAfter;
From that result set you can see that for every inventory ID we get the Price Before, and the Price After the update statement was run. All this with a single TSQL statement.
I hope at this point you have a good understanding of using the OUTPUT clause with UPDATE statements.
See Also:
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply