add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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