CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Jayme
Jayme

Reputation: 1946

How do I update a group of rows excluding the latest item in that group

I need to be able to update every row in my table where a specific field matches, but not the latest row of the matching group.

so lets say for

Example:

I have 10 rows:

I need to update 4 rows in each matching group leaving the latest item untouched.

No values are fixed, I will not know how many rows there will be or how many groups, the above is just an example.

Thank you for your help.

Upvotes: 1

Views: 70

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use ROW_NUMBER() to enumerate rows and a CTE to define the to be updated result set. Using the row number you can selectively update the first 4 rows of each partition:

;WITH ToUpdate AS (
   SELECT Field1,
          ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY myOrderColumnn) AS rn
   FROM mytable
   WHERE Field1 IN (23, 56)
)
UPDATE ToUpdate 
SET Field1 = 'myValue'
WHERE rn <= 4

If you need to exclude the last item and you don't know the precise population of each partition then you can reverse the ordering and filter out the first record:

;WITH ToUpdate AS (
   SELECT Field1,
          ROW_NUMBER() OVER (PARTITION BY Field1 
                             ORDER BY myOrderColumnn DESC) AS rn
   FROM mytable
   WHERE Field1 IN (23, 56)
)
UPDATE ToUpdate 
SET Field1 = 'myValue'
WHERE rn > 1

Upvotes: 2

Related Questions