[table] is a table which I parse once a minute, and I am updating the oldest records with 'in progress'. When I am done processing, I delete the 'in progress' records and start over. My rare issue is when I have the same record trying to get imported twice in the same 50 batch. I would like to change this.

I have this update of [table]

;WITH cte AS (
SELECT  TOP 4   [Status]
FROM            [table] 
ORDER BY        [ImportDate] ASC)
UPDATE cte SET [Status] = 'IN PROGRESS';

I would like to add a second update (99% of cases would not trigger, and when it would, it will most certainly will be 3 records maximum), where any of these 50 IN PROGRESS records that are dupes based on a column called UniqueId, would be updated back to New (the oldest record would keep the value IN PROGRESS, the newest ones would be NEW)

Example:

before

after

desired 2nd update's result

Note unique id is unique in the original database, in my table is not unique, my table has no indexes, no constraints or anything, since it is empty almost all the time, except when we get data dumped in it

How can I do that?

CodePudding user response：

If I understand correctly you can try to use Row_NUMBER window function twice instead of TOP, the first Row_NUMBER window generate number of row which can filter instead TOP

Another Row_NUMBER window to update first ImportDate each UniqueId

;WITH cte AS (
    SELECT *
    FROM
    (
        SELECT  [Status], 
                ROW_NUMBER() OVER(PARTITION BY UniqueId ORDER BY ImportDate) rn1,
                ROW_NUMBER() OVER(ORDER BY ImportDate) rn2
        FROM  [table]
    ) t1    
    WHERE rn1 = 1 AND rn2 BETWEEN 1 AND 4
)
UPDATE cte 
SET [Status] = 'IN PROGRESS'

sqlfiddle