Developers often face situations when they find their column have duplicate records and they want to delete it. A good developer will never delete any data without observing it and making sure that what is being deleted is the absolutely fine to delete. Before deleting duplicate data, one should select it and see if the data is really duplicate.
In this video we are demonstrating two scripts – 1) selects duplicate records 2) deletes duplicate records.
We are assuming that the table has a unique incremental id. Additionally, we are assuming that in the case of the duplicate records we would like to keep the latest record. If there is really a business need to keep unique records, one should consider to create a unique index on the column. Unique index will prevent users entering duplicate data into the table from the beginning. This should be the best solution. However, deleting duplicate data is also a very valid request. If user realizes that they need to keep only unique records in the column and if they are willing to create unique constraint, the very first requirement of creating a unique constraint is to delete the duplicate records.
Let us see how to connect the values in Sixty Seconds:
Here is the script which is used in the video.
CREATE TABLE TestTable (ID INT, NameCol VARCHAR(100))
INSERT INTO TestTable (ID, NameCol)
SELECT 1, 'First'
SELECT 2, 'Second'
SELECT 3, 'Second'
SELECT 4, 'Second'
SELECT 5, 'Second'
SELECT 6, 'Third'
-- Selecting Data
-- Detecting Duplicate
SELECT NameCol, COUNT(*) TotalCount
GROUP BY NameCol
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
-- Deleting Duplicate
WHERE ID NOT IN
GROUP BY NameCol)
-- Selecting Data
DROP TABLE TestTable
Related Tips in SQL in Sixty Seconds:
- SQL SERVER – Delete Duplicate Records – Rows
- SQL SERVER – Count Duplicate Records – Rows
- SQL SERVER – 2005 – 2008 – Delete Duplicate Rows
- Delete Duplicate Records – Rows – Readers Contribution
- Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON – A Transactional Behavior
What would you like to see in the next SQL in Sixty Seconds video?
Reference: Pinal Dave (