SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #Customers
    (
        CustomerID INT ,
        FirstName VARCHAR (25) ,
        LastName VARCHAR (25) ,
        CustomerStatusTypeID VARCHAR (1) ,
        isMerged BIT ,
        MergedTo INT
    );
INSERT INTO #Customers
VALUES ( 1, 'Kevin', 'Smith', 'M', 1, 2 ) ,
       ( 2, 'Kevin', 'Smith', 'M', 1, 3 ) ,
       ( 3, 'Kevin', 'Smith', 'M', 1, 4 ) ,
       ( 4, 'Kevin', 'Smith', 'O', 0, NULL ) ,
       ( 5, 'Mary', 'Jones', 'O', 0, NULL ) ,
       ( 6, 'Wyatt', 'Earp', 'M', 1, 7 ) ,
       ( 7, 'Wyatt', 'Earp', 'O', 1, NULL ) ,
       ( 8, 'Bruce', 'Wayn', 'M', 1, 10 ) ,
       ( 9, 'Brice', 'Wayne', 'M', 1, 10 ) ,
       ( 10, 'Bruce', 'Wane', 'M', 1, 11 ) ,
       ( 11, 'Bruce', 'Wayne', 'O', 1, NULL );
11 rows affected

SELECT *
FROM   #Customers;


CustomerID
FirstName
LastName
CustomerStatusTypeID
isMerged
MergedTo


1
Kevin
Smith
M
True
2

2
Kevin
Smith
M
True
3

3
Kevin
Smith
M
True
4

4
Kevin
Smith
O
False
null

5
Mary
Jones
O
False
null

6
Wyatt
Earp
M
True
7

7
Wyatt
Earp
O
True
null

8
Bruce
Wayn
M
True
10

9
Brice
Wayne
M
True
10

10
Bruce
Wane
M
True
11

11
Bruce
Wayne
O
True
null


UPDATE c1
       SET c1.mergedto = x.customerid
       FROM #customers c1
            LEFT JOIN (SELECT c2.customerid,
                              soundex(c2.firstname) sefn,
                              soundex(c2.lastname) seln,
                              row_number() OVER (PARTITION BY soundex(c2.firstname),
                                                              soundex(c2.lastname)
                                                 ORDER BY c2.customerid DESC) rn
                              FROM #customers c2) x
                      ON x.sefn = soundex(c1.firstname)
                         AND x.seln = soundex(c1.lastname)
                         AND x.rn = 1
                         AND x.customerid <> c1.customerid;
11 rows affected

SELECT *
       FROM #customers;


CustomerID
FirstName
LastName
CustomerStatusTypeID
isMerged
MergedTo


1
Kevin
Smith
M
True
4

2
Kevin
Smith
M
True
4

3
Kevin
Smith
M
True
4

4
Kevin
Smith
O
False
null

5
Mary
Jones
O
False
null

6
Wyatt
Earp
M
True
7

7
Wyatt
Earp
O
True
null

8
Bruce
Wayn
M
True
11

9
Brice
Wayne
M
True
11

10
Bruce
Wane
M
True
11

11
Bruce
Wayne
O
True
null

CustomerID	FirstName	LastName	CustomerStatusTypeID	isMerged	MergedTo
1	Kevin	Smith	M	True	2
2	Kevin	Smith	M	True	3
3	Kevin	Smith	M	True	4
4	Kevin	Smith	O	False	null
5	Mary	Jones	O	False	null
6	Wyatt	Earp	M	True	7
7	Wyatt	Earp	O	True	null
8	Bruce	Wayn	M	True	10
9	Brice	Wayne	M	True	10
10	Bruce	Wane	M	True	11
11	Bruce	Wayne	O	True	null