Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL : How to update top N records in a table?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 13.1k
    Comment on it

    It is a common requirement in SQL server development to update top N records in SQL server.In this blog we will see two approaches to accomplish the same.

    1) Using UPDATE with TOP

      UPDATE TOP (100) Table1 SET  field1 = 1
    

    However without an ORDER BY the whole idea of TOP is vague, since there's no way to guarantee WHICH top N rows we are updating.

    2) Using CTE

    ;WITH CTE AS 
    ( 
      SELECT TOP 100 * 
      FROM Table1  
      ORDER BY F2 
    ) 
    UPDATE CTE SET  field1=1
    

    In the above query we are using the ORDER BY on column F2 before updating. So now we have control on which N rows we are updating.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: