mardi 14 juin 2016

SQL Query table with multiple records with matching columns

I have a SQL database with a table that has phone number records with a few fields

PersonGUID (Fk, uniqueidentifier)
PhoneSequence (int)
PhoneTypeCode (Fk)
AreaCode 
PhoneNumber
PhoneGUID (Pk, uniqueidentifier)

It's normal for there to be multiple records for each PersonGUID but we had an error when importing a bulk list of phone number records where the PhoneSequence was set as 1 for the same PersonGUID records. These people have multiple records where PhoneTypeCode='Mobile' and PhoneTypeCode='Home' and both are marked with PhoneSequence=1 so the system assumes they have two primary phone numbers.

I want to create a query that will search the table for anyone that has multiple records but cannot figure out how to say "find people that have both a home and mobile phone and both records' sequence is 1"

Inevitably, I'd like to update the Home phone records (for users that have both) so the sequence is 2.

This is the closest I've been able to figure out:

update tPersonPhone
set PhoneSequence = 2
from tPersonPhone pp
where PhoneTypeCode ='home' and
exists (select * from PersonPhone pp1 
        where pp.PersonPhoneGUID = pp1.PersonPhoneGUID 
        and PhoneTypeCode in ('home','mobile') 
        and PhoneSequence = 1)

Thank you

Aucun commentaire:

Enregistrer un commentaire