SQL Server 2008 - Updating only a specific number of rows within a table

count, select, sql, sql-server

Solution

One easy way is using `ROW_NUMBER` in a `CTE`, e.g.:

WITH CTE AS
(
   SELECT rn = ROW_NUMBER() OVER (ORDER BY ref, id),
          ref, id, text
   FROM   MyTable
   WHERE  ref = 555 AND  id = 7
)
UPDATE CTE SET text = 'Matched' 
WHERE RN <= 400

Demo

Problem

I have a table called mytable with a number of columns. What I am trying to do is to update a column within that table where 2 colums are matched but only for the first 400 records that match the criteria. Example: Columns ``` Name Ref ID Text ``` When ref = 555 and id = 7 I want to update the column called Text with the word 'Matched'. I am aware that there will be approx 800 records that match the criteria but I only want to update the first 400. Can anyone help with this please? Regards, Will.

Original source