CODEWITHSUNDEEP
sqlsql-servert-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Update all except last two?

I have a CTE that looks like the following:

;with cte as  
(  
    SELECT  WeekNum
    FROM MonitorTable 
    GROUP BY WeekNum
    HAVING SUM(CASE WHEN IsProcessed = 1 THEN 1
               ELSE 0
          END) = 8
    order by WeekNum
) 
update MonitorTable 
set ReadToGrid = 0 
where WeekNum in (select WeekNum from cte)

The issue here is that it's updating everything. Since there always has to be two left, I can only update everything except the last.

So let's say that CTE returns the following:

1
2
3
4
5
6

I cannot update everything; would need to update everything except the last two (1, 2, 3, 4).

So how can I modify the script so that it updates everything except the last two rows?

Thanks.

Upvotes: 0

Views: 61

Answers (1)

Joe C
Joe C

Reputation: 3993

Without data I cant test the exact syntax but the idea will work:

Select WeekNum, Row_Number() Over (Order By WeekNum Desc) RowNum
From
(
SELECT  WeekNum
    FROM MonitorTable 
    GROUP BY WeekNum
    HAVING SUM(CASE WHEN IsProcessed = 1 THEN 1
               ELSE 0
          END) = 8
)
update MonitorTable set ReadToGrid = 0 where WeekNum in (select WeekNum from cte Where RowNum > 2)

Upvotes: 1

Related Questions