2017年5月16日 星期二

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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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’.
1
2
3
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.
1
2
3
4
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:
1
2
3
4
5
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:
1
2
3
4
5
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:

from : http://stevestedman.com/2015/02/tsql-output-clause-with-update-statements/
於

沒有留言:

張貼留言