Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

SQL command to remove not distinct records in MS SQL

How can I delete those records with the same ID, I just want to retain unique record in one particular column.

E.x.

I have these records:

custID    FirstName                   LastName
1           Jose                          Fabella
1          James                        Smith
2          Sharon                      Williams
3          Nick                           Joaquin
2          Tim                            Burton

After deleted, it should retain 1,2,3 records - it's irrelevant who will be deleted on the duplicate custID as long as it will retain one of each


0
jr_bautista
Asked:
jr_bautista
2 Solutions
 
reb73Commented:
Try -

DELETE t1
FROM tablename t1
LEFT JOIN (SELECT CustId, MIN(FirstName) as MinFirstName, MIN(LastName)  as MinLastName, COUNT(*)  DupCount 
	   FROM tablename GROUP BY CustId HAVING COUNT(*) > 1) t2 
	ON t2.CustId = t1.CustId and t2.MinFirstName = t1.FirstName and t2.MinLastName = t1.LastName
WHERE t2.CustId IS NULL

Open in new window

0
 
dportasCommented:
What is/are the key(s) in your table? It generally helps to specify the keys when asking such a question. Try:

WITH t AS
 (SELECT custID, FirstName, LastName,
 ROW_NUMBER() OVER (PARTITION BY custID ORDER BY FirstName, LastName) r
 FROM tbl)
DELETE FROM t WHERE r > 1;

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now