sql-server - 从值为空的同一表更新项目

Question

我有一个表成员，其中有重复的条目，我想删除其中一个，但其中一个条目更新了一些列，另一个更新了一些其他列。

所以我想做的是更新，但值存在于 1 中的列和另一个中存在 null 或空的列，以便两行变得完全相同，并且我不会丢失任何数据。

表结构:

CREATE TABLE [dbo].[membermobilenumberisnull](
[TransId] [bigint] NOT NULL,
[member_id] [int] NOT NULL,
[gendertype] [int] NULL,
[relationship_rs_code] [nvarchar](2) NULL,
[ration_card_id] [int] NOT NULL,
[uid] [nvarchar](16) NULL,
[member_dob] [datetime] NULL,
[member_name_en] [nvarchar](150) NULL,
[mother_name_en] [nvarchar](150) NULL,
[father_name_en] [nvarchar](150) NULL,
[member_age] [smallint] NULL,
[nationality] [nvarchar](150) NULL,
[MobileNumber] [nvarchar](20) NULL,
[IsUpdated] [bigint] NULL,
[UpdationDate] [datetime] NULL,
[IsDeleted] [bigint] NULL,
[DeletionDate] [datetime] NULL,
[CreationDate] [datetime] NULL,
[UpdatedBy] [nvarchar](250) NULL,
[DeletedBy] [nvarchar](250) NULL,
[MobileNumber1] [nvarchar](20) NULL,
[MobileFlag] [nvarchar](250) NULL
) ON [PRIMARY]

GO

示例数据

TransId     member_dob  member_name_en      member_age  nationality MobileNumber    UpdationDate    CreationDate
252238402   12-09-1985  PUSHPINDER SINGH    31          IND                         NULL            30-07-2016
252238403   12-09-1985  PUSHPINDER SINGH    31          IND         8626934377      NULL            30-07-2016
260846102   03-06-1984  VUDDHI DEVI         32          IND         9459209701      19-10-2016      14-08-2016
260846105   03-06-1984  VUDDHI DEVI         32          IND         NULL                            14-08-2016

预期结果:

TransId     member_dob  member_name_en      member_age  nationality     MobileNumber    UpdationDate    CreationDate
252238402   12-09-1985  PUSHPINDER SINGH    31          IND             8626934377      NULL            30-07-2016
252238403   12-09-1985  PUSHPINDER SINGH    31          IND             8626934377      NULL            30-07-2016
260846102   03-06-1984  VUDDHI DEVI         32          IND             9459209701      19-10-2016      14-08-2016
260846105   03-06-1984  VUDDHI DEVI         32          IND             9459209701      19-10-2016      14-08-2016

提前致谢

附加示例:

Answer 1

假设您的组合 PK 是 member_dob,member_name_en,member_age

UPDATE  M1
SET     M1.MobileNumber     =   ISNULL(NULLIF(M1.MobileNumber,''),M2.MobileNumber)
        ,M1.UpdationDate    =   ISNULL(M1.UpdationDate,M2.UpdationDate)
FROM    Member  AS  M1
    INNER JOIN  Member  AS  M2  ON  M1.TransId  <>  M2.TransId
                                AND M1.member_dob = M2.member_dob
                                AND M1.member_name_en = M2.member_name_en
                                AND M1.member_age = M2.member_age