从SQL数据库中删除重复的记录，保留最旧的记录 [英] Removing duplicate records from SQL database keeping the oldest record

问题描述

你好，

我已经安装了一个将数据保存到SQL数据库的C#应用​​程序.我在应用程序中有一个按钮，用于检查数据库中是否有重复的记录，然后通过检查将它们保存到SQL数据库时添加到每个记录的日期时间戳来删除最新记录.
我现在在数据库中有大量记录，并且想要修改DELETE语句，如前所述:这将检查数据库中的所有记录，并删除带有最新日期时间戳的重复记录.
我现在需要修改此语句，以仅检查记录的最后6个月，并忽略是否有六个月以上的重复记录.
我有以下SELECT语句，它确实返回了我希望DELETE语句关注的值:

Hi there,

I have setup a C# application which saves data to a SQL database. I have a button in the application that checks the database for any duplicate records then removes the latest record, by checking the datetime stamp that is added to each record when they are saves to the SQL database.
I now have a great number of records within the database and would like to modify the DELETE statement, which as mentioned previously: this checks all records in the database and removes the duplicate record with the latest datetime stamp.
I now need to modify this statement to only check the last 6 months of records and to ignore if there is any duplicates older than six months.
I have the following SELECT statement which does return the values that I would like the DELETE statement to focus on:

WHERE createdDate >= (DATEADD(mm,-6,GETDATE())) AND createdDate <= GETDATE()

这是我之前设置的用于从数据库中删除重复项的语句，但这将检查数据库中的所有记录，因此我需要在该语句中包括一个日期范围.

This is the previous statement that I had setup for removing duplicates from the database, but this checks all records in the database, I need to include a date range into this statement.

DELETE FROM tblAddress
WHERE A_Id NOT IN (SELECT MIN(A_Id) FROM tblAddress GROUP BY colAddress)
SELECT * FROM tblAddress ORDER BY createdDate ASC

SQL数据库为保存到数据库的每个记录包括以下列:
A_Id =自动生成的ID号，整数，PK
colAddress = varChar(255)用于存储地址
createdDate = datetime，用于跟踪将记录添加到数据库的时间.

我需要将此日期范围包括在WHERE子句下的现有语句中，以删除记录(如果有较旧的记录，但从现在起的6个月内):
当前要更改的声明:

The SQL database includes the following Columns for each record saved to the database:
A_Id = Auto generated ID number, int, PK
colAddress = varChar(255) used to store address
createdDate = datetime, used to track when the record was added to the database.

I need to include this date range into the pre-existing statement under the WHERE clause for removing records, if there is a duplicate record that is older, but within the 6 month range from now:
Current statement to be altered:

DELETE FROM tblAddress 
WHERE A_Id NOT IN (SELECT MIN(A_Id) FROM tblAddress GROUP BY colAddress)

WHERE子句中需要包含的日期范围:

date range that needs to be included in the WHERE clause:

WHERE createdDate >= (DATEADD(mm,-6,GETDATE())) AND createdDate <= GETDATE()

推荐答案

查找伪代码-添加您自己的日期时间差异逻辑

Find the pseudo code - Add your own date time difefrence logic

DELETE FROM Table WHERE A_Id NOT IN
( 
SELECT MAX(A_Id) FROM table WHERE createdDate > 6 months
GROUP BY Id 

)

请参考，


http://www.simple -talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/ [

Hi,

Refer this,


http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/[^]

使用以下查询.

Hi,

Use the below query.

DELETE FROM tblAddress WHERE A_Id NOT IN (SELECT MIN(A_Id) FROM tblAddress GROUP BY colAddress) AND createdDate >= (DATEADD(mm,-6,GETDATE());

希望这能奏效.

然后再次检查结果
SELECT * FROM tblAddress ORDER BY createdDate

Hope this will work.

Then check the result again
SELECT * FROM tblAddress ORDER BY createdDate

这篇关于从SQL数据库中删除重复的记录，保留最旧的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！