How to UPDATE TOP 400 ?

Question

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

Answer 1

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.

Answer 2

 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 dont like this as there's no way to guarentee WHICH top 400, you might want to add some other type of criteria. And even an Order By to the subquery.

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

You can use following syntax

UPDATE top (400) tbl SET column1 = '2' WHERE column2 = '1' AND column1 is null

See this post http://balasingam.com/sql-server/update-top-n-record-in-sql-server/comment-page-1#comment-227