How can I update the top 100 records in SQL server

sql

update-records

sql-server

performance-optimization

byAnton Shumikhin·Oct 11, 2024

Curious about updating the top 100 rows? Harness common table expressions (CTEs) to select your targets, then enable UPDATE. Here's the shovel-ready SQL:

WITH TopRows AS (
    SELECT TOP 100 * FROM YourTable ORDER BY YourCriteria DESC
)
UPDATE TopRows SET YourColumn = 'NewValue'

Just replace YourTable, YourCriteria (the sorting rationale), and YourColumn with your actual names, and fill in NewValue with your desired update. By using ORDER BY, we make sure we're giving our updates the march of an army — fully under control.

Advanced Techniques for Updating Top Rows

Before making a mad dash and updating rows, especially in tables that are a Swiss bank for data, let's consider some advanced tips to blend performance with accuracy.

Specific updates with subqueries

Need to laser-target a particular subset of rows? Load this blaster:

UPDATE YourTable
SET YourColumn = 'DesiredValue'
WHERE id IN (
    SELECT TOP 100 id FROM YourTable WHERE PandoraBox = 'ConditionValue' ORDER BY GoldenTicketColumn DESC
)

This SQL sequence emulates a queen bee — it only chooses the fittest using a condition and order in the subquery, giving you full power to command your data.

Locking horns with lock contention

In a world where "concurrent" is a buzzword, updating in batches is your secret weapon to reduce lock conflict:

WHILE (1 = 1)
BEGIN
    UPDATE TOP (100) YourTable
    SET YourColumn = 'HotCakeValue'
    WHERE SomeCondition = true
    IF @@ROWCOUNT < 100 BREAK
END

This piece of code — dear reader, is the Jason Bourne of SQL. Silent, but regular. It updates 100 rows at a time until the job is done.

Magic of table-valued functions (TVFs)

When your selection criteria feels like solving Sudoku, consider table-valued functions (TVFs):

UPDATE t
SET t.YourColumn = 'NewValue'
FROM YourTable t
INNER JOIN dbo.YourTVF(Parameters) tvf ON t.id = tvf.id

TVF — your ally in the dual against complex logic — packages it neatly, can integrate TOP, accepts dynamic alterations, and yes, the name sounds cool.

Important Factors to Consider

To feed the nerdy side of things, here are a few vital technical aspects when updating records in SQL server.

Sequenced Updates with CTEs

Fans of order will appreciate pairing ORDER BY with TOP within a CTE:

WITH OrderedRecords AS (
    SELECT TOP 100 *, ROW_NUMBER() OVER (ORDER BY LuckyDraw DESC) AS rn
    FROM YourTable
)
UPDATE OrderedRecords SET YourColumn = 'NewValue'

This approach adheres to the principle "First come, first served." With an efficient, sequenced UPDATE, you have a disciplined force marching — not a mob rushing.

Beware the deprecated `SET ROWCOUNT`

'SET ROWCOUNT' after SQL Server 2005 feels like enjoying a movie that has long lost its popcorn appeal:

-- A blast from the past
SET ROWCOUNT 100
UPDATE YourTable SET YourColumn = 'NewValue'
SET ROWCOUNT 0

The cool kids now use CTE or subqueries instead.

Indexing for Efficiency

Leaving no stone unturned, remember to index the ORDER BY column:

-- 'GoldenTicketColumn' better be indexed
WITH TopRows AS (
    SELECT TOP 100 * FROM YourTable ORDER BY GoldenTicketColumn DESC
)
UPDATE TopRows SET YourColumn = 'ChampagneValue'