locked
Updating a specific amount of records in a table RRS feed

  • Question

  • I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.

    Thanks,

    Wednesday, June 20, 2007 7:42 PM

Answers

  • If you are using SQL Server 2005, you can even use the new TOP(N),e.g.

    UPDATE TOP(N)
    SOMETable
    SET SomeColumn = 'A'
    WHERE Condition = Something


    Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Friday, June 22, 2007 12:57 PM

All replies

  • First, we need to know how you define the "first 5,000" rows. Remember, a table has no concept of order or things like first, last, previous, next, etc.  You have to use oner or more of the columns to determine first.  I will provide an example that may point you in the right direction:

     

    Say we have a table like the following:

     

    Code Snippet

    CREATE TABLE test

    (

       testID  INT PRIMARY KEY,

       InsertDate Datetime,

       ColumnA INT

    )

     

    Given that, if you want to update columnA (I will add 1 to it in this example) for the first 5,000 rows with the lowest InsertDate, the statement could be as follows:

     

    Code Snippet

    UPDATE test

    SET ColumnA = ColumnA + 1

    WHERE testID IN

       ( SELECT TOP 5000 testID -- This dervived table will select the Primary Keys for

         FROM test                         -- the first 5,000 rows as ordered by the InsertDate column

         ORDER BY InsertDate )

     

     

     

    Thursday, June 21, 2007 5:16 AM
  • If you are using SQL Server 2005, you can even use the new TOP(N),e.g.

    UPDATE TOP(N)
    SOMETable
    SET SomeColumn = 'A'
    WHERE Condition = Something


    Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Friday, June 22, 2007 12:57 PM
  • Thanks to both of you for your posts, I should have said I wanted to update 5000 records at a time, it could have been any.
    Tuesday, June 26, 2007 10:26 PM