How to Update top N records in Sql Server

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.

Dataset

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.

Syntax

UPDATE TOP (N) table_name 
SET column_name = value;

Example

We are going to proceed to update the top 10 CreditLimit records.

UPDATE TOP (10) Sales.Customers
SET CreditLimit = '100';

Results

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.

Example

WITH C AS(
SELECT TOP (10) CustomerID
	  ,CreditLimit 
FROM Sales.Customers
ORDER BY CustomerID
)
UPDATE C
SET CreditLimit = 100;

Results

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.

Example

UPDATE Sales.Customers
SET CreditLimit = 100
WHERE CustomerID IN(
		SELECT TOP (10) CustomerID 
		FROM Sales.Customers
		ORDER BY CustomerID)

Results

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.

Example

SET ROWCOUNT 10
UPDATE Sales.Customers
SET CreditLimit = 100
SET ROWCOUNT 0;