如何将计数的行标记为已删除?续上一个问题

How to mark counted rows as deleted? contin#39;d from previous question(如何将计数的行标记为已删除?续上一个问题)
本文介绍了如何将计数的行标记为已删除?续上一个问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

好的,我刚刚从我之前的问题中意识到这一点:根据辅助表中的行更新主表

Ok I just realized that from my previous question: Update main table based on rows from a secondary table

那是如何使用辅助表更新主表的计数,但由于此查询将在 sql 作业中,我如何删除我刚刚计算的行,并确保我不删除任何新的行插入辅助表?

That was how to update the main table with the count using the secondary table, but since this query will be in a sql job, how can I delete the rows I just counted, and making sure I don't delete any new rows inserted into the secondary table?

推荐答案

如果我没看错,您想(在您的第一个问题中)使用与每个 EmployeeID 关联的唯一 IP 数更新 SelectionCount,现在在同一过程中,从 Selection 表中清除构成这些计数的记录 - 同时不触及自 SelectionCount 更新以来添加的任何记录.如果您的选择表具有 1) 主键和 2) 行的创建日期,这将容易得多.两者的缺失使事情变得复杂.

If I'm reading it right, you want to (in your first question) update the SelectionCount with the number of unique IPs associated with each EmployeeID, and now in the same process clear out the records from the Selection table that made up those count - while simultaneously not touching any records added since the SelectionCount was updated. This would be a lot easier if your selection table had 1) a primary key, and 2) a date the row was created. The absence of both complicates things.

因此,我们将采用一种稍微奇怪的方式(假设您无法更改架构).这假设您使用的是 SQL 2005 或 2008 :

So we'll do this a slightly odd way (assuming you're not able to change your schema). This assumes you're using SQL 2005 or 2008 :

我在这里猜测你的数据类型

I'm guessing your datatypes here

DECLARE @TempIPs TABLE(EmployeeID int, ipaddress varchar(25))

DELETE FROM Selection
OUTPUT deleted.EmployeeID, deleted.ipAddress
INTO @TempIPs

--用保存旧 IP 的 @TempIPs 替换对选择表的引用.

--replace reference to Selection table with @TempIPs which holds the legacy IPs.

UPDATE Employee
SET SelectionCount = IsNull((SELECT Count(DISTINCT ipAddress) 
    FROM @TempIPs
    WHERE @TempIPs.EmployeeID = Employee.ID), 0)

如果您计划在添加新记录时继续增加 SelectionCount,只需将 + SelectionCount 添加到您的 UPDATE 语句中.您还应该将整个批次包装在交易 (BEGIN TRANSACTION....COMMIT TRANSACTION) 语句中.

If you're planning to keep increasing the SelectionCount as new records are added, just add + SelectionCount to your UPDATE statement. You should also wrap the whole lot inside a transaction (BEGIN TRANSACTION....COMMIT TRANSACTION) statement.

这篇关于如何将计数的行标记为已删除?续上一个问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Query with t(n) and multiple cross joins(使用 t(n) 和多个交叉连接进行查询)
Unpacking a binary string with TSQL(使用 TSQL 解包二进制字符串)
Max rows in SQL table where PK is INT 32 when seed starts at max negative value?(当种子以最大负值开始时,SQL 表中的最大行数其中 PK 为 INT 32?)
Inner Join and Group By in SQL with out an aggregate function.(SQL 中的内部连接和分组依据,没有聚合函数.)
Add a default constraint to an existing field with values(向具有值的现有字段添加默认约束)
SQL remove from running total(SQL 从运行总数中删除)