Update Item from same table where values are null

Question

I have a table Member where their are duplicate entries and I want to remove one of them, but 1 of the entry has some columns updated and another has some other.

So What I want to do is update but the columns where values exist in 1 and null or empty in another so that both the rows become completely identical and I do not loose any data out of it.

Table Structure:

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

Sample Data

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

Expected Result:

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

Thanks in advance

Example attached:

Answer 1

Assume your combined PK is 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

Answer 2

Try this

WITH t2 AS (
SELECT member_dob, member_name_en, member_age, nationality, MobileNumber
FROM <<table>>
WHERE MobileNumber IS NOT NULL
)
UPDATE t1
SET MobileNumber = t2.MobileNumber
FROM <<table>> t1 JOIN t2
ON t1.member_dob = t2.member_dob
AND t1.member_name_en = t2.member_name_en
AND t1.member_age = t2.member_age
AND t2.nationality = t2.nationality
WHERE t1.MobileNumber IS NULL

Answer 3

It seems you want to look at a member_name_en and take a value from whichever of its records, preferring non-null over null. Then simply aggregate and use MIN or MAX to pick such value.

update m
  set member_dob = src.max_member_dob
    , member_age = src.max_member_age
    , nationality = src.max_nationality
      etc.
from membermobilenumberisnull m
(
  select 
    member_name_en,
    max(member_dob) as max_member_dob,
    max(member_age) as max_member_age,
    max(nationality) as max_nationality,
    etc.
  from membermobilenumberisnull
  group by member_name_en
) src on src.member_name_en = m.member_name_en;