1

Yes, another SQL duplicates question :)

I have a SQL Server 2008 R2 table with multiple phone number columns, looking something like:

ID Tel1 Tel2 Tel3 Tel4 Tel5 Tel6 1 123 456 789 NULL NULL NULL 2 123 456 123 123 NULL NULL 3 456 789 123 456 NULL NULL

I'd like to remove the duplicate phone numbers from each row - for example, in row ID 2, I need to NULL Tel3 and Tel4, and in row 3 I need to NULL Tel4. I don't need to check for duplicates between rows - the same phone number can exist between in multiple rows, just not in different columns in the same row.

Can anyone suggest the best way to get rid of these duplicates?

Improve this question

3 Answers 3

Reset to default
2

Sql Fiddle here.

update PhoneNumbers set Tel2 = case when Tel1 = Tel2 then null else Tel2 end, Tel3 = case when Tel3 in (Tel1, Tel2) then null else Tel3 end, Tel4 = case when Tel4 in (Tel1, Tel2, Tel3) then null else Tel4 end, Tel5 = case when Tel5 in (Tel1, Tel2, Tel3, Tel4) then null else Tel5 end, Tel6 = case when Tel6 in (Tel1, Tel2, Tel3, Tel4, Tel5) then null else Tel6 end
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@NikolaMarkovinović +1 didn't though to do it all in one step, nice one! :)
1

You can find them using UNPIVOT...

select id, telNo from phonenumbertable unpivot ( telNo for tel in (tel1, tel2, tel3, tel4,tel5, tel6)) as u group by id,telno having COUNT(telno)>1
Improve this answer

1 Comment

OK, that'd help me find duplicates, but what about NULL'ing them out?
1

One possible way is to update it like this:

update tablename set Tel6 = null where Tel6 = Tel5 or Tel6 = Tel4 or Tel6 = Tel3 or Tel6 = Tel3 or Tel6 = Tel2 or Tel6 = Tel1

and then do the same update (with fewer comparisons in the where clause) to the other columns (except the first one).

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.