重复条目的清理更新 [英] Cleanup Update for Duplicate Entries

问题描述

我有一个包含用户信息(UserInfo)的表，有些用户可能在不同的UserIds下两次出现在表中，如下所示:

I have a table that contains user information (UserInfo), and some users may be in the table twice under different UserIds, like so:

UserId       LastName       FirstName       active
000001       Jetson         George          1
000002       Flintstone     Fred            0
000003       Jetson         George          1
000004       Flintstone     Fred            1
000005       Barbara        Hannah          1

每个用户只有 1 行应显示为活动状态，但应用程序中的一个错误导致有两个用户条目的唯一 UserId 设置为 1.

Only 1 row per user should show as active, but a bug in the application is causing there to be two user entries with unique UserIds that have active set to 1.

然后我有另一个表，其中包含与 UserId (UserRecords) 关联的记录.在重复的活动 UserIds 的情况下，只有两个 UserIds 之一将返回第二个表中的任何结果.因此:

I then have another table that contains records associated with a UserId (UserRecords). In the case of duplicate active UserIds, only one of the two UserIds will return any results in the second table. Thus:

SELECT 
((SELECT count(*)
FROM UserRecords recs
where recs.UserId= inf.UserId)) as Records, *
FROM UserInfo inf
where inf.lastname = 'Jetson' and
inf.active='1' and
inf.firstname='George'

可能会返回:

Records       UserId       LastName       FirstName       active
0             000001       Jetson         George          1
1273          000003       Jetson         George          1

我想创建一个更新语句，将任何条目的活动列更改为 0 where active = '1' and Records='0'，但是有数百个用户具有重复的条目，以后可能还会有更多.所以我想创建一个更新语句来找到这些并将它们自动设置为 0.

I want to create an update statement to change the active column to 0 for any entries where active = '1' and Records='0', but there are hundreds of users that have duplicate entries, and there could be more later. So I would like to create an update statement that would find these and set them to 0 automatically.

我拥有的是

update UserId
set active = '0'
where (SELECT count(*)
FROM UserRecords recs
where recs.UserId= inf.UserId) = 0

该语句的问题在于它没有考虑重复用户.可能有些用户在 UserRecords 中(还)没有记录，但也没有重复的条目.将它们设置为 0 会导致系统问题.

The problem with that statement is that it does not take into account duplicate users. There could be users who simply have no records in UserRecords (yet) but that also do not have duplicate entries. Setting those to 0 would cause system problems.

那么，如何将我的更新语句更改为仅在存在重复条目的情况下将活动标记为 0?

So, how can I change my update statement to only mark active as 0 where there are duplicate entries?

任何帮助将不胜感激.

谢谢！

推荐答案

好的，以下是我的建议.您想更改 where 子句以仅指定重复项.此外，您真的只想查看活动记录，因为是否存在非活动记录的重复并不重要.

Okay, so here's what I would recommend. You want to change your where clause to specify only the duplicates. Also, you really only want to look at the active records because it doesn't matter if there are duplicates of inactive records.

为了查看是否有重复，您可以使用exists.为了使用exists，首先我们要编写一个子查询来拉回重复的记录，也就是任何具有相同名字和姓氏、不同id、并且也是活动的.如果子查询拉回一些东西，exists 将返回 true，我们将更新记录.如果没有重复，子查询将不会抓取任何记录，并且存在将返回 false.然后，我们不会更新记录.

In order to see if there is a duplicate, you can use an exists. In order to use the exists, first we are going to write a subquery to pull back duplicate records, aka anything with the same first and last name, a different id, and is also active. If the subquery pulls something back, exists will return true and we will update the record. If there are no duplicates, the subquery will not grab any records and the exists will return false. Then, we will not update the record.

update u
set active = 0
From UserInfo u
where (SELECT count(*)
FROM UserRecords recs
where recs.UserId= u.UserId) = 0
and     u.active = 1
and     exists (Select 1
                From UserInfo u2
                Where u2.lastname = u.lastname
                and     u2.firstname = u.firstname
                and     u2.userid <> u.userid
                and     u2.active = 1)

这篇关于重复条目的清理更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！