英文:
How to add a trigger to delete duplicates in table
问题
以下是翻译好的内容：
"Hi，我有一个如下所示的表格：
[dbo].[table1](
[predictions] [int] NULL,
[timestamp] [datetime] NULL,
[train1] [int] NULL,
[train2] [int] NULL)
我正在使用 dbx job 向这个表格插入数据，有时候在表格中会出现时间戳列的重复项。
当发生重复项时，我想在数据库中添加一个触发器来删除它们。
现在，我正在手动运行以下查询来删除重复值：
With duplicates As (Select *, ROW_NUMBER() Over (PARTITION by timestamp Order by timestamp) as Duplicate From table1) select From duplicates Where Duplicate > 1 ;
非常感谢任何帮助！"
英文:
Hi I have a table like this below:
[dbo].[table1](
[predictions] [int] NULL,
[timestamp] [datetime] NULL,
[train1] [int] NULL,
[train2] [int] NULL)
I'm inserting data into this table with dbx job..and sometimes we get duplicates for timestamp column in the table.
I want to add a trigger to the db to delete duplicates when it happens.
I'm manually deleting duplicate values running this query below now.
With duplicates As (Select *, ROW_NUMBER() Over (PARTITION by timestamp Order by timestamp) as Duplicate From table1) select From duplicates Where Duplicate > 1 ;
Any help is greatly appreciated!
答案1
得分: 4
你可以使用不太为人知的 IGNORE_DUP_KEY 选项。
ALTER TABLE dbo.table1
ADD CONSTRAINT uq_table1_timestamp
UNIQUE (timestamp)
WITH (IGNORE_DUP_KEY = ON);
这将自动（并静默地）忽略重复的行插入。
请注意，这不会删除现有的重复项，您首先需要运行：
WITH cte AS (
SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY timestamp ORDER BY timestamp)
FROM yourTable
)
DELETE cte
WHERE rn > 1;
英文:
You can use the little known IGNORE_DUP_KEY option.
ALTER TABLE dbo.table1
ADD CONSTRAINT uq_table1_timestamp
UNIQUE (timestamp)
WITH (IGNORE_DUP_KEY = ON);
This will automatically (and silently) ignore inserts of rows which are duplicates.
Note that this does not remove existing duplicates, you first need to run:
WITH cte AS (
SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY timestamp ORDER BY timestamp)
FROM yourTable
)
DELETE cte
WHERE rn > 1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论