SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Puzzling SQL query using MERGE statement


Puzzling SQL query using MERGE statement

Author
Message
S_Kumar_S
S_Kumar_S
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 1062
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
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14464 Visits: 15980
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
S_Kumar_S
S_Kumar_S
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 1062
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
Nevyn
Nevyn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1538 Visits: 3149
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.
S_Kumar_S
S_Kumar_S
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 1062
thx a lot..it worked..

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search