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

6jjcrrmo 于 2023-03-28 发布在其他

关注(0)|答案(2)|浏览(150)

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 |

sql-server

来源：https://stackoverflow.com/questions/75864494/remove-oldest-record-in-sql-table-when-there-is-more-than-1-record-with-the-same

2条答案

按热度按时间

wmvff8tz1#

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 .

赞(0）回复(0）举报 2023-03-28

hgtggwj02#

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;

赞(0）回复(0）举报 2023-03-28

我来回答

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

2条答案

相关问题

热门标签

最新问答