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

Question

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.

TableName	TransferTime
Table A	2023-03-28 10:00
Table A	2023-03-28 10:15
Table B	2023-03-28 10:00
Table B	2023-03-28 10:15
Table C	2023-03-28 10:00

What happens if there are three or more rows per `TableName` — Charlieface, Mar 28 '23 at 11:50

score 1 · Accepted Answer · answered Mar 28 '23 at 09:36

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;

score 0 · Answer 2 · answered Mar 28 '23 at 09:33

You can use EXISTS to check rows before deletion:

DELETE FROM Table1 t1
WHERE
    exists (
        select
            1
        from
            TABLE1 t2
        where
            t2.TableName = t1.TableName
            and t2.TransferTime > t1.TransferTime
    )

This query deletes row if exists row with the same TableName but bigger TransferTime.

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

2 Answers2