SQL Change an UPDATE to skip duplicates-CodePudding

[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 50  [Status]
FROM            [table] 
ORDER BY        [ImportDate] ASC)
UPDATE cte SET [Status] = 'IN PROGRESS';

I would like to change it in a way that if there are 2 (or more) records in the [table] with the same unique ID, then just get the oldest one, just one record all the time, so it's like an update distinct uniquecolumn order by importdate

Note unique id is unique in the original database, in my table is not unique, my table has no indexes, no constraint 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：

One wasy would be to rank order all [status] values by ImportDate and date the oldest using ROW_NUMBER().

;WITH cte AS (
    SELECT TOP 50 * FROM
    (
        SELECT  [Status], 
                RN= ROW_NUMBER() OVER(PARTITION BY Status ORDER BY ImportDate)
        FROM    [table] 
    )AS X       
    WHERE RN=1
)
UPDATE cte SET [Status] = 'IN PROGRESS'