Member Avatar for Member #871145

I have a table with roughly 50,000 records. Each record has 25 or so columns. One of which is a phone number column. Some of these records, while most not not the same in any other way (a few are identical), have the same phone number. Id like to delte all but one record from the table so I only have one record per phone number. Thoughts?

  • 2 Contributors
  • 3 Replies
  • 119 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by CrappyCoder

Dani AI

Generated

A few practical, low-risk steps to remove duplicates while keeping the row you want (echoing the goal you stated) and to guard production data.

You said you want one row per phone number, prefer the row with the largest ACCT_BAL, and to set IMPORT_DT to today. is right that you can aggregate and join to identify winners; another common pattern is a single CTE with ROW_NUMBER() so you can preview, delete, then update in a controlled way. Normalize phone numbers first (strip non-digits, trim, canonicalize country codes) and exclude NULL/blank phones so formatting differences don’t leave duplicates behind.

Preview and delete pattern (replace ID and YourTable with your PK/table names):

WITH Ranked AS (
  SELECT ID, TelephoneNo, ACCT_BAL,
         ROW_NUMBER() OVER (PARTITION BY TelephoneNo ORDER BY ACCT_BAL DESC, ID ASC) AS rn
  FROM dbo.YourTable
)
SELECT * FROM Ranked WHERE rn > 1;  -- preview rows that would be removed

After you verify the preview, remove duplicates and then set IMPORT_DT on the remaining rows:

WITH Ranked AS (
  SELECT ID,
         ROW_NUMBER() OVER (PARTITION BY TelephoneNo ORDER BY ACCT_BAL DESC, ID ASC) AS rn
  FROM dbo.YourTable
)
DELETE T
FROM dbo.YourTable AS T
JOIN Ranked R ON T.ID = R.ID
WHERE R.rn > 1;

UPDATE dbo.YourTable
SET IMPORT_DT = GETDATE()
WHERE IMPORT_DT IS NULL;  -- adjust condition as needed

Final notes: always backup or run on a copy first; test the SELECT preview; consider tie-breakers when ACCT_BAL is equal (above uses lowest ID); watch triggers and foreign keys; for large tables do this in batches and consider adding a unique index on the normalized phone column afterward to prevent re-duplication.

Recommended Answers

All 3 Replies

Member Avatar for CrappyCoder

How would you know which record to keep if the phone numbers are the same but the record information is different?

Do you have a timestamp on each record so you know whioh is the latest record?

Member Avatar for Member #871145

For our purposes here which record is kept really doesnt matter. We woud want the following:

"IMPORT_DT" = Todays date

and you could take the field "ACCT_BAL" and simply take the largest value of the records with the same Phone Number values.

Does that make sense?

Member Avatar for CrappyCoder

The following code...

SELECT	MAX(ACCT_BAL),
         [TelephoneNo]
FROM     [TableName]
GROUP BY [TelephoneNo]
HAVING COUNT(TelephoneNo)>1

Will return all the duplicate telephone numbers and the Largest account balance - you can then put the results of this in a temporary hash table and join onto your original table. Record with the same telephone number but lesser ACCT_BALs can then be deleted from your original table.

Other ways of doing this but really depends on yer SQL knowledge

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.