Logo
Back

How can I update top 100 records in SQL server?

In SQL Server, you can limit the number of rows updated using the TOP clause. However, you need to be aware that specifying which rows qualify for the “top” 100 can require additional logic—by default, SQL Server does not guarantee an order unless you specify one through a workaround (for example, using a subquery or a CTE). Below are a few approaches you can use, with tips on how to control which rows get updated.

1. Using UPDATE TOP (100)

UPDATE TOP (100) YourTable
SET SomeColumn = 'NewValue'
WHERE <Condition>;
  • Pros: Simple syntax.
  • Cons: SQL Server does not allow an ORDER BY clause in an UPDATE statement directly. If no ORDER BY logic is applied externally, which 100 rows get updated can be nondeterministic.

Controlling the Rows to Update

If you only have a WHERE clause but no ordering, SQL Server decides which 100 rows match the condition first—this is not always predictable. To influence which rows belong to the “top” 100, you can use a subquery or CTE approach.

2. Using a Subquery with an ORDER BY

One workaround is to nest a SELECT statement (with ORDER BY) that pinpoints the exact rows you want:

WITH OrderedCTE AS 
(
    SELECT TOP (100) p.PrimaryKeyColumn
    FROM YourTable p
    WHERE <Condition>
    ORDER BY p.SomeColumn ASC
)
UPDATE YourTable
SET SomeColumn = 'NewValue'
FROM YourTable t
JOIN OrderedCTE o
    ON t.PrimaryKeyColumn = o.PrimaryKeyColumn;
  1. CTE (OrderedCTE) selects the top 100 rows in a deterministic order.
  2. The UPDATE then targets only those 100 rows by matching on the primary key.
  3. This method ensures you know exactly which rows get updated.

3. Best Practices and Considerations

  1. Use Primary Keys or Unique Identifiers: Always identify rows uniquely if you care about a specific set of 100 records (e.g., smallest date, largest ID, etc.).
  2. Transactions: For critical updates, wrap your operations in a transaction, especially if you plan to update multiple batches of 100 rows.
  3. Concurrency: Be aware that concurrent transactions or row locking could alter which rows end up in your top 100 if your condition is dynamic.
  4. Testing: Always run test queries on a development/staging environment or use a SELECT statement first (e.g., SELECT TOP (100) ...) to see which rows will be updated.

4. Further SQL Learning with DesignGurus.io

For those looking to sharpen SQL query skills, manage database transactions effectively, and tackle more advanced scenarios:

  1. Grokking SQL for Tech Interviews – Dive into query patterns, edge case handling, and performance tuning essential for interviews.
  2. Grokking Database Fundamentals for Tech Interviews – Master indexing strategies, normalization, and transaction management to keep your database efficient under load.

You can also watch free tutorials on the DesignGurus.io YouTube channel. If you’re preparing for high-stakes tech interviews, consider their Mock Interviews program, offering personalized feedback from ex-FAANG engineers.

Conclusion

To update the top 100 records, you can use UPDATE TOP (100) ... directly—but be aware it offers no built-in ORDER BY. If you need precise control over which rows are included, combine a SELECT TOP (100) (with ORDER BY) inside a CTE or subquery, then match and update those rows. This approach ensures deterministic results and gives you a clear view of which records are modified.

CONTRIBUTOR
TechGrind