Sample Data Table
In order to show you how to use the TOP clause in an UPDATE, INSERT or DELETE statement I will need to create some sample data. My sample data will contain a stock level table. My stock level table maintains the quantity of items that I have on hand for each product. I will use the code below to create and populate my sample data:
CREATE TABLE StockLevel (ProductId int, Qty int); INSERT INTO StockLevel VALUES (1,10),(2,12), (3,1), (4, 79);
In the code above I created the StockLevel table and populated it with 4 different rows. The StockLeveltable shows how much stock I have available for each product. Each row contains the ProductID and theQty columns. The ProductID column contains the product identification number for the product, and theQty column shows the number of product items I have in my current inventory.
Updating Stock Level Using the UPDATE Statement
The UPDATE statement supports the TOP clause just like the SELECT statement. To demonstrate this, suppose I want to update the StockLevel table created in the prior section using the following business rules:
- Update StockLevel rows only if the Qty column value is less than 15.
- Increase the Qty column by 10 additional items when adding inventory.
- At max only update 2 rows
To meet these requirements I will run the following code:
-- rows before UPDATE SELECT * FROM StockLevel; -- update Stocklevel UPDATE TOP (2) StockLevel SET QTY = QTY + 10 WHERE Qty < 15; -- rows after UPDATE SELECT * FROM StockLevel;
When I run the code above I get the following results from the first SELECT statement:
ProductId Qty ----------- ----------- 1 10 2 12 3 1 4 79
By reviewing this output you can see my requirements will have me updating ProductId 1 and 2 only. NotProductId 3 because that would be the third row updated, and we only wanted to update 2 rows using the TOP clause. We can review the results of the UPDATE statement by looking at the following output from the second SELECT statement:
ProductId Qty ----------- ----------- 1 20 2 22 3 1 4 79
Here you can see that ProductId 1 and 2 did in fact get their Qty amount increased by 10. Notice thatProductId 3 didn’t get its product Qty value increased, even though its Qty value is 1. This happened because I used the TOP (2) clause on the UPDATE statement. Having the TOP (2) clause told SQL Server to update only two rows. Since two rows had already been updated (ProductId 1 and 2) my update statement didn’t add additional inventory to ProductId 3.
What Happens When You Add a TOP Clause on an INSERT Statement?
I can add the TOP clause to an INSERT statement just like I could on an UPDATE statement. When I do that SQL Server will insert the appropriate number of rows. To demonstrate this consider the following code:
-- create table to hold TOP(2) CREATE TABLE ProductIdList (ProductId int, Qty int); -- insert TOP(2) INSERT TOP(2) into ProductIdList SELECT ProductID, Qty FROM StockLevel; -- see what got inserted SELECT * FROM ProductIdList;
When I run this code I get the following output:
ProductId Qty ----------- ----------- 1 20 2 22
If you review the code you will see that I first created a new table named ProductIdList. This table was populated using an INSERT statement that uses a TOP(2) clause. All four records from the StockLevel table were passed as potential records to be inserted using a SELECT statement. But since the INSERT statement had the TOP(2) clause only two rows were inserted. By reviewing the output you can see that only ProductID 1 and 2 got inserted into the ProductIdList table.
In the code above SQL Server randomly selected the TOP(2) rows to be inserted into the ProductIdListtable. Rows were randomly inserted because there was no ORDER BY clause on the SELECT statement. Suppose you wanted to insert the two rows with the highest Qty value. How would this be handled?
If I need to create a ProductIDList2 table that contains two rows that had the largest Qty values you would think I could just run this code:
-- create table to hold TOP(2) CREATE TABLE ProductIdList2 (ProductId int, Qty int); -- insert TOP(2) INSERT TOP(2) into ProductIdList2 SELECT ProductId, Qty FROM StockLevel ORDER BY Qty DESC; -- see what got inserted SELECT * FROM ProductIdList2;
When I run this code I get the following output:
ProductId Qty ----------- ----------- 1 20 2 22
By reviewing this output you can see this code didn’t place the top 2 Qty values into my ProductIdList2table. Why is this? This happened because of where I placed the “TOP(2)” clause. In this example I placed it on the INSERT statement. Which told SQL Server to randomly insert 2 rows from my ordered record set. Which isn’t exactly what I wanted.
In order to get the two rows with highest Qty value I would need to run the following code:
-- create table to hold TOP(2) CREATE TABLE ProductIdList3 (ProductId int, Qty int); -- insert TOP(2) INSERT into ProductIdList3 SELECT TOP(2) ProductID, Qty FROM StockLevel ORDER BY Qty DESC; -- see what got inserted SELECT * FROM ProductIDList3;
Here I placed the TOP(2) clause on my SELECT clause that feeds the INSERT statement. Doing this feeds only the top two records based on the Qty value. Care needs to be taken when determining where you are going to place the TOP clause in your query when you want to restrict rows, as demonstrated in this section.
Using TOP Clause on DELETE Statement
There are times when you might just want to delete a set of records based on the top so many records in a table. But just as we identified on the INSERT statement, when a TOP clause is used on a DELETE statement it performs similarly, meaning rows are randomly deleted. To demonstrate this let’s run the following code:
BEGIN TRANSACTION; SELECT * FROM StockLevel; DELETE TOP(1) FROM StockLevel; SELECT * FROM StockLevel; ROLLBACK TRANSACTION;
When I run this code I got the following two sets of output from each SELECT statement:
ProductId Qty ----------- ----------- 1 20 2 22 3 1 4 79 ProductId Qty ----------- ----------- 2 22 3 1 4 79
By reviewing the output above you can see that my DELETE TOP(1) statement deleted ProductId 1. Which just happens to be the first record in the record set, not based on the Qty value. Using the BEGIN TRANSACTION followed by a ROLLBACK TRANSACTION allowed me to rollback my actual delete statement so we could rewrite the code to delete the row with the lowest Qty value.
To accomplish deleting the StockLevel record with the lowest Qty column value I would need to run the following code:
SET NOCOUNT ON; DELETE FROM StockLevel WHERE ProductId in (SELECT TOP(1) ProductId FROM StockLevel ORDER BY Qty); SELECT * FROM StockLevel;
When I run this code I get the following output:
ProductId Qty ----------- ----------- 1 20 2 22 4 79
As you can see I have now deleted ProductId value of 3. I did this by identifying the ProductId’s to delete based on the record with the lowest the Qty column value, which in my example was the Stocklevel row with a ProductValue of 3. I accomplished this by identifying the records to delete based on a correlated subquery. My correlated sub-query uses the TOP (1) criteria to identify the one ProductId that is to be deleted.
Conclusion
As was demonstrated in this article the TOP clause can be used in conjunction with UPDATE, INSERT and DELETE statements. Although care should be taken to make sure the TOP clause will identify the correct records for a given INSERT, UPDATE or DELETE operation. Next time you need to INSERT, UPDATE, or DELETE X number of records you might consider how you can use the TOP clause to accomplish the logic needed..
No comments:
Post a Comment