A typical sql server update consist in updating a set of data filtered by the where clause but sometimes we only need to update the top N records without affecting the others.
We are going to use ‘Sales.Customers’ table from WideWorldImporters database.
Next, we are going to update CreditLimit column and set it to NULL
1. Updating top N records using Top (N)
The update statement of Sql Server supports the use of TOP clause to specify the number of records to update.
UPDATE TOP (N) table_name SET column_name = value;
We are going to proceed to update the top 10 CreditLimit records.
UPDATE TOP (10) Sales.Customers SET CreditLimit = '100';
As you can see CustomerIDs where updated on a random order.
The order of records that are going to be updated is not certain when using TOP N as rows as order can change each time you execute this query.
2. Updating top N records using a CTE
By using a common table expression CTE we can select which top 10 values we would like to be updated.
This is possible by selecting the top 10 values in the inner CTE part.
Unlike using TOP (N), by using Order by in the inner CTE, we can select the order on which rows are going to be updated.
WITH C AS( SELECT TOP (10) CustomerID ,CreditLimit FROM Sales.Customers ORDER BY CustomerID ) UPDATE C SET CreditLimit = 100;
Updated values are ordered by CustomerID, this order is not random.
3. Updating top N records using Subqueries
This option is pretty similar than using a CTE, but the difference here is that we need a column on which we are going to filter the updated values.
UPDATE Sales.Customers SET CreditLimit = 100 WHERE CustomerID IN( SELECT TOP (10) CustomerID FROM Sales.Customers ORDER BY CustomerID)
Results are similar than using a CTE as the order on which columns are going to be updated is established by using Order by in the INNER query.
4. Updating top N records using ROWCOUNT
This option is available since Sql Server 2005 and available on Sql Server 2019
I do not recommend using it because ROWCOUNT use is confusing and this should not be a reliable option as columns can’t be ordered before updating them.
SET ROWCOUNT 10 UPDATE Sales.Customers SET CreditLimit = 100 SET ROWCOUNT 0;