Remove oldest record in SQL table when there is more than 1 record with the same name

I’ve a Pipeline in Synapse which syncs data from a source SQL database to a destination SQL database. As soon as the copy data activity is done it inserts a record (with a stored procedure) in a log table.

This table is used to sync data incremental. As soon as I start my PipeLine again it will compare the date in this log table with the DateChanged date of the source table so that it knows which records to sync.

At the end of the PipeLine run I would like to delete the record with the oldest TransferTime. So that I keep my table clean and small. But I only want to do this when there are more than 1 record for the table. Just to prevent that my whole pipeline fails because it can’t find a last TransferTime, so it doesn’t know which records are changed synce last run.

In the example below I want to delete the oldest records of Table A and Table B which have the TransferTime 2023-03-28 10:00.

>Solution :

Exists provides one canonical way to do this. Given that you are using SQL Server, you could also use a deletable CTE here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY TableName
                                 ORDER BY TransferTime DESC) rn
    FROM yourTable
)

DELETE
FROM cte
WHERE rn > 1;