HiI have an emergency Phone Numbers table and it has got some duplicate records. Duplicate is defined as same PersonId and same PhoneId. Unfortunately duplicate phone is incorrctly defined as both IsPrimaryNumber=1 as well as IsPrimaryNumber=0. I have to delete/Update duplicates with following rules:Rule 1:If IsPrimaryNumber=1, then final output should have this flag set to 1.Rule 2:If phoneExtesnion is present in any of the records, it should be returned.If it is present in multiple records, we may pick any.Rule 3:Rule 2 applies to Comments column as well.

INSERT INTO EmergencyPhoneNumber VALUES(1,'4EE56555-91C6-4755-AC8E-0099D297445A',2771,0,'1111','this is phone1')INSERT INTO EmergencyPhoneNumber VALUES(2,'4EE56555-91C6-4755-AC8E-0099D297445A',2771,1,'','this is phone2')INSERT INTO EmergencyPhoneNumber VALUES(3,'4EE56555-91C6-4755-AC8E-0099D297445A',2772,0,1111,'this is phone3')

Where you have a duplicate, how do you decide which one to throw away? And why do you want to use MERGE? MERGE is used for updating, inserting and/or deleting in the same statement. You've just asked for a result set, which will be a SELECT statement.

HiI was thinking to use MERGE because you are basically merging 2 records. As I of the mentioned in 3 rules, we can delete any of the record, provided it has been merged in the record which will stay.So if the IsPrimaryNumber=1 in the record we choose to delete, then the record which stays should have IsPrimaryNumber=1 if it was 0 earlier.

Pls let me know if I need to provide more elaboration.

one addition: I basically want the table to finally have the records which I mentioned as output of SELECT. Sorry about confusion.

INSERT INTO #EmergencyPhoneNumber VALUES(1,'4EE56555-91C6-4755-AC8E-0099D297445A',2771,0,'1111','this is phone1')INSERT INTO #EmergencyPhoneNumber VALUES(2,'4EE56555-91C6-4755-AC8E-0099D297445A',2771,1,'','this is phone2')INSERT INTO #EmergencyPhoneNumber VALUES(3,'4EE56555-91C6-4755-AC8E-0099D297445A',2772,0,1111,'this is phone3')

;WITH numbertelns AS ( SELECT ROW_NUMBER() OVER (PARTITION BY PersonID, PhoneID ORDER BY PersonPhoneId) AS rownum, PersonId, PhoneId, IsPrimaryNumber, PhoneExtension, Comments FROM #EmergencyPhoneNumber), grouptelns AS ( SELECT PersonId, PhoneId, MAX(PhoneExtension) AS PhoneExtension, MAX(CAST (IsPrimaryNumber AS TINYINT)) AS IsPrimaryNumber FROM #EmergencyPhoneNumber GROUP BY PersonId,PhoneId), keeprow AS ( SELECT p1.rownum,p1.PersonId,p1.PhoneId,CAST(p2.IsPrimaryNumber AS bit) AS IsPrimaryNumber,p2.PhoneExtension,p1.Comments FROM numbertelns p1 JOIN grouptelns p2 ON p1.PersonId = p2.PersonId AND p1.PhoneId = p2.PhoneId WHERE p1.rownum = 1)