UPDATE records in a table except the TOP 1 record

Question

Hi All,

I have business scenario as

1. We will get all the data to the database including the duplicates

2. If we have any duplicated in a table take the most recent record from duplicates on perticular key by making all the remaining deplicate records flag to 'X'

3. While processing to the next level filter the extraction by flag != 'X' so we can get only one most recent record from all the duplicate for a perticulat key.

How can we update all the records except the TOP 1 record.

any thoughts

thanks

prav

Answer 1

Not sure if this is what you mean:

UPDATE table
  SET yourstuff
WHERE yourclauses
    AND table.ID <> (select TOP 1 ID from table where yourclauses)

Answer 2

Assuming that you must have column(s) that can be used to unambiguously identify the "most recent" (called datecol below)

UPDATE  YourTable
   SET YourTable.[flag] = 'X'
 FROM  YourTable t1
 WHERE (not exists(
                  select * from YourTable t2 
                  where t2.[key] = t1.[key] and 
                  t2.datecol > t2.datecol))

Answer 3

An updateable cte:

with cte as (
select *, row_number () over (partition by foo order by bar) as rn
from table)
update cte
set flag = x
where rn > 1;