Sql-server – Merging Sql Server Rows

sql server

I have a SQL Server table with Customer Names and Phone Numbers. Customers will be an owner or party member.
There are chances where owners and party member give the same mobile number. I wanted to check the table and if the mobile number is duplicated then I need to remove it from the table. How can I achieve it?

CustomerName   MobileNumber
----------------------------
AAA             +447000000000
BBB             +447000000000
CCC             +447000000000

Result I expect:

CustomerName   MobileNumber
----------------------------
AAA             +447000000000
BBB             NULL
CCC             NULL

Best Answer

WITH CTE AS (
    SELECT CustomerName 
        ,MobileNumber
        ,ROW_NUMBER() OVER (PARTITION BY MobileNumber ORDER BY CustomerName) AS rn
    FROM yourTable
)
UPDATE CTE
SET MobileNumber = NULL
WHERE rn > 1;

This query uses common table expression to group rows by MobileNumber and assign them numbers in chosen order. I chose order by CustomerName to get the same results as in a question description. After that it updates all rows with row number greater than 1 and set MobileNumber to NULL for them.

Related Question