Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Puzzling SQL query using MERGE statement Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 6:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:28 AM
Points: 360, Visits: 839
Hi
I 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.

Below is table and data:
CREATE TABLE EmergencyPhoneNumber(PersonPhoneId int Primary Key,
PersonId uniqueidentifier,
PhoneId int,
IsPrimaryNumber bit,
PhoneExtension varchar(10),
Comments varchar(255))

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')

Expeacted output for above records is:

PersonPhoneId PersonId PhoneId IsPrimaryNumber PhoneExtension Comments
1 4EE56555-91C6-4755-AC8E-0099D297445A 2771 1 1111 this is phone1
3 4EE56555-91C6-4755-AC8E-0099D297445A 2772 0 1111 this is phone3

PS:I'll prefer if it can be done with MERGE statement.
thanks for help


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1532027
Posted Friday, January 17, 2014 6:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 5,074, Visits: 8,907
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.

John
Post #1532029
Posted Friday, January 17, 2014 6:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:28 AM
Points: 360, Visits: 839
Hi
I 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.


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1532033
Posted Friday, January 17, 2014 7:45 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 546, Visits: 1,876
My first pass:

CREATE TABLE #EmergencyPhoneNumber(PersonPhoneId int Primary Key,
PersonId uniqueidentifier,
PhoneId int,
IsPrimaryNumber bit,
PhoneExtension varchar(10),
Comments varchar(255))

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)

MERGE numbertelns n1
USING keeprow n2
ON n1.PersonId = n2.PersonId
AND n1.PhoneId = n2.PhoneId
AND n1.rownum = n2.rownum
WHEN MATCHED THEN
UPDATE SET n1.PhoneExtension = n2.PhoneExtension,
n1.IsPrimaryNumber = n2.IsPrimaryNumber
WHEN NOT MATCHED BY SOURCE THEN DELETE
;


SELECT * FROM #EmergencyPhoneNumber

DROP TABLE #EmergencyPhoneNumber

The extension logic may be undesired. You didn't say what to do if the duplicates both had extensions and they did not match. In that case I take the highest one.

A merge actually does make some sense here, as you need to both update and delete.
Post #1532064
Posted Friday, January 17, 2014 11:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:28 AM
Points: 360, Visits: 839
thx a lot..it worked..

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1532193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse