合并表中的记录并更新相关表

Question

我有一个主表，其中包含链接到各种其他表的用户。有时，由于错误的导入数据，此主表中存在重复项，我希望合并它们。请参阅下表。

表:用户

UserID    Username    FirstName    LastName
1         Main        John         Doe
2         Duplicate   John         Doo

表: Records1

RecordID  RecordName      CreatedUserID   UpdatedUserID
1         Test record 1   1               2
2         Test record 2   2               null
3         Test record 3   2               null

CreatedUserID和UpdatedUserID是Users.UserID的外列。

UPDATE Records1 SET UpdatedUserID = 1 WHERE UpdatedUserID = 2
UPDATE Records1 SET CreatedUserID = 1 WHERE CreatedUserID = 2
DELETE FROM Users WHERE UserID = 2

这只是一个样例子集，但实际上，有很多相关的记录表，我必须为它们添加额外的SQL-Update语句。

users表基本上是链接到所有其他表的基表，因此为每个表创建单独的语句是相当麻烦的，所以如果有快捷方式，那就太好了。

Answer 1

这对你有帮助吗？

    Create Table Users(Id int, UserName varchar(10),FirstName varchar(10), LastName Varchar(10))
    Create Table Records1(RecordID int,  RecordName varchar(20), CreatedUserID int,   UpdatedUserID int)


    INSERT INTO Users
    SELECT 1,'Main','John','Doe' Union All
    SELECT 2,'Duplicate','John','Doo' Union All

    SELECT 3,'Main3','ABC','MPN' Union All
    SELECT 4,'Duplicate','ABC','MPT' 


    Insert into Records1
    SELECT 1,'Test record 1',1,2    Union All
    SELECT 2,'Test record 2',2,null Union All
    SELECT 3,'Test record 3',2,null Union All

    SELECT 1,'Test record 1',3,4    Union All
    SELECT 2,'Test record 2',4,null Union All
    SELECT 3,'Test record 3',4,null 

    Select u1.Id as CreatedUserID,U2.id as UpdatedUserID
    Into #tmpUsers 
    from Users u1
    JOIN Users u2 
    --This Conidition Should be changed based on the criteria for identifying Duplicates
    on u1.FirstName=u2.FirstName and U2.UserName='Duplicate'
    Where u1.UserName<>'Duplicate'


    Update r
    Set r.UpdatedUserID=u.CreatedUserID
    From Records1 r
    JOIN #tmpUsers u on r.CreatedUserID=u.CreatedUserID

    Update r
    Set r.CreatedUserID=u.CreatedUserID
    From Records1 r
    JOIN #tmpUsers u on r.CreatedUserID=u.UpdatedUserID

    Delete from Users Where UserName='Duplicate'

    Select * from Users
    Select * from Records1


    Drop Table #tmpUsers

Answer 2

由于识别重复帐户的过程将是手动的，因此(通常)将有成对的帐户需要处理。(我假设Inspector不能在您的UI中勾选15个用户帐户作为重复帐户，然后提交整个批次进行处理。)

下面这样的存储过程可能是一个好的开始：

create procedure MergeUsers
  @RetainedUserId Int, -- UserId   that is being kept.
  @VictimUserId Int -- UserId   that is to be removed.

as
  begin

  -- Validate the input.
  --   Optional, but you may want some reality checks.
  --   (Usernames are probably unique already, eh?)
  declare @UsernameMatch as Int, @FirstNameMatch as Int, @LastNameMatch as Int, @EmailMatch as Int;
  select
    @UsernameMatch = case when R.Username = V.Username then 1 else 0 end,
    @FirstNameMatch = case when R.FirstName = V.FirstName then 1 else 0 end,
    @LastNameMatch = case when R.LastName = V.LastName then 1 else 0 end,
    @EmailMatch = case when R.Email= V.Emailthen 1 else 0 end
    from Users as R inner join
      Users as V on V.UserId = @VictimUserId and R.UserId = @RetainedUserId;
  if @UsernameMatch + @FirstNameMatch + @LastNameMatch + @EmailMatch < 2
    begin
    -- The following message should be enhanced to provide a better clue as to which user
    --   accounts are being processed and what did or didn't match.
    RaIsError( 'MergeUsers: The two user accounts should have something in common.', 25, 42 );
    return;
    end;

  -- Update all of the related tables.
  --   Using a single pass through each table and updating all of the appropriate columns may improve performance.
  --   The   case   expression will only alter the values which reference the victim user account.
  update Records1
    set
      CreatedUserId = case when CreatedUserId  = @VictimId then @RetainedUserId else CreatedUserId end,
      UpdatedUserId = case when UpdatedUserId = @VictimId then @RetainedUserId else UpdatedUserId end
    where CreatedUserId = @VictimUserId or UpdatedUserId = @VictimUserId;

  update Records2
    set ...
    where ...;

  -- Houseclean   Users .
  delete from Users
    where UserId = @VictimUserId;

  end;

NB：左侧练习是在SP中添加try/catch和事务，以确保合并是一个全有或全无操作。