Q:

Update Statement Query

I have table called status which has data like this:
ID   Status  Number
1    S       1
1    p       2
1    S       3
1    C       4
2    S       5
2    R       6

I wanted to get rid of duplicates in this table so I added another column to this table called Flag and I set this to 1 for all the records. Now my table looks like this:
ID   Status  Number  Flag
1    S       1       Y
1    p       2       Y
1    S       3       Y
1    C       4       Y
2    S       5       Y
2    R       6       Y

I am writing an update query on this to update the flag column to N if it has duplicates and the update query is:
The Number column is identity in the table.
udate status
set flag = 'N'
where flag = 'Y' and Number in 
(select min(Number) from status
where id = 1
having count(*) >1)

So If do I this query it gives me data without duplicates
Select status from status
where flag = Y

But the update query doesn't work if i have 3 similar values for status. The update works only if i have 2 similar values for status.
Can anyone suggest me a way to do this?

A:

Use this to flag duplicates only (leaving the first value untouched):
WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY id, status ORDER BY number) AS rn
        FROM    status
        )
UPDATE  q
SET     flag = CASE rn WHEN 1 THEN 'Y' ELSE 'N' END

or this to flag all values having duplicates:
WITH    q AS
        (
        SELECT  *, COUNT(*) OVER (PARTITION BY id, status) AS cnt
        FROM    status
        )
UPDATE  q
SET     flag = CASE cnt WHEN 1 THEN 'Y' ELSE 'N' END

