How to UPDATE TOP 400?

19,825

Solution 1

 UPDATE db.dbo.tbl SET column1 = 2 WHERE
 primaryID IN (
   SELECT TOP (400) primarkyID FROM db.dbo.tbl
   WHERE column2 = 1 AND column1 IS NULL
 )

But I don't like this as there's no way to guarantee WHICH top 400, you might want to add some other type of criteria. And even an Order By to the subquery.

Solution 2

How would you determine the top 400? With no order by there is no guanantee that the same set would always be selected and thus the wrong records could be updated.

Solution 3

WITH    q AS
        (
        SELECT  TOP 400 *
        FROM    db.dbo.tb
        WHERE   column2 = 1
                AND column1 is null
        ORDER BY
                column3 -- choose your order!
        )
UPDATE  q
SET     column2 = 2

Solution 4

You're probably looking for something like this:

update db.dbo.tbl set column1 = 2 
where ID in (
  select top 400 ID from db.dbo.tbl
  where column2 = 1 and column1 is null --the criteria have been moved here
  order by ID --order by clause recommended
  )

where ID is the primary key column of the table.

Solution 5

If you're using SQL Server 2008, the "top n" syntax will work on delete and update statements. Otherwise, the other methods listed here where you identify the primary keys in a subquery or derived table will work well. And as others have done, the "order by" is highly recommended or the rows you update can differ from one query to the next.

Share:
19,825

Related videos on Youtube

Admin
Author by

Admin

Updated on June 04, 2022

Comments

  • Admin
    Admin over 1 year

    I would like to update the top 400 rows in a database table. The pseudo SQL is below, how can I do this?

    UPDATE top (400) db.dbo.tbl
    SET column1 = 2
    WHERE column2 = 1
      AND column1 is null
    
  • pjp
    pjp about 14 years
    You can also use set rowcount 400 instead of using TOP in the query see msdn.microsoft.com/en-us/library/ms188774.aspx
  • Dave Carlile
    Dave Carlile about 14 years
    Except that SET ROWCOUNT is deprecated.
  • pjp
    pjp about 14 years
    @Dave: Are you sure? I can't see that documented in the MSDN docs msdn.microsoft.com/en-us/library/ms188774.aspx
  • Dave Carlile
    Dave Carlile about 14 years
    @pjp: If you scroll down a bit you'll find this text under Remarks: Important: Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL).