How to find row values before and update or delete query in SQL Server

+1 vote
asked Jun 13, 2013 in SQL Server by anonymous

I am really wondering, whether below given query is what best method to get the row values before and after we update or delete query. If some one knows better way getting this result, pls share it with me.

Below is the way I find the values
Consider I have a table called product with productid, price, count

ProductId , Price , ItemCount
(1 , 2.00 , 40)
(2 , 10.00 , 20)
(3 , 5.0 , 28)
(4 , 8, 25)

Now consider, I have received 10 more items on each product which has the price <= 5, so I update my table with the new value. But to keep track what was the count before update and what is the count after update I use below query.

SELECT ProductId, ItemCount INTO #BeforeUpdate FROM Product WHERE Price <= 5
UPDATE Product SET ItemCount = ItemCount + 10 WHERE Price <= 5
SELECT ProductId, ItemCount INTO #AfterUpdate FROM Product WHERE Product ID IN (SELECT ProductId FROM #BeforeUpdate)

Now I can compare both the temporary table and figure out what happened.

Share

1 Answer

+1 vote
answered Jun 14, 2013 by Aadhira (1,213 points)
selected Jun 18, 2013 by administrator
 
Best answer

I think an easy way to do this functionality is using Output Clause. This Output clause can be used in Insert, Update and Delete statement. During this operation it will give us 2 pseudo tables called "inserted" and "deleted" tables. "Inserted" table will have the information after the query executed and "deleted" table will have the information before the query gets executes. So, you can achieve the same functionality using the below code.

UPDATE Product 
SET ItemCount = ItemCount + 10 
OUTPUT inserted.ProductId, deleted.ItemCount as [old count], inserted.ItemCount as [New count]
WHERE Price <= 5

Your output would be
ProductId , Old count, New count
(1 , 40 , 50)
(3 , 28 , 38)

If you want to use it inside your stored procedure and use the result for some other process in the same stored procedure then you may want to create some table variable at use the result for further process

declare @compare table
(
 ProductId int not null,
 [Old count] int,
 [New count] int
)

UPDATE Product 
SET ItemCount = ItemCount + 10 
OUTPUT inserted.ProductId, deleted.ItemCount as [old count], inserted.ItemCount as [New count] INTO @compare
WHERE Price <= 5

SELECT * FROM @compare

Your output would be same as previous
ProductId , Old count, New count
(1 , 40 , 50)
(3 , 28 , 38)

Note: OUTPUT clause must be used after last SET and before WHERE Condition

Your answer

Preview

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.
site design / logo / content © 2013 - 2015 pinfaq.com
...