Delete Duplicate Rows in SQL Table

Published: 03 February 2020
on channel: The SQL Guy

1,485

This tutorial covers how to find duplicate records within SQL Server and how to delete them! I've also covered a few other points such as a query on how to find duplicates and how not to delete them!

HOW TO DELETE DUPLICATES USING A CTE

Personally I think the best, easiest and quickest way to delete duplicate rows is to use a CTE with the ROW NUMBER function. Its easier to analyse the data beforehand to ensure your query is correct but also ensures that anything with more than one record is removed! Obviously like anything in SQL Server there are a load of ways of doing the same thing, some quicker than others but I like simplicity and cte's give us that!

HOW NOT TO DELETE DUPLCATE RECORDS IN SQL

Whats just as important is to understand how NOT TO delete duplicate records as this can lead to data loss. So I've covered two ways of doing this and explained what dangers and pitfalls to look out for by comparing a SQL CTE with a GROUP BY and HAVING clause. This will show you how one method removes a duplicate while the other will remove both rows from the table

HOW TO PREVENT DUPLICATE RECORDS

The lesson also shows how to use PRIMARY KEYS to prevent duplicates being inserted in the first place. This can save us all a great deals of headaches just by setting up some good database design practices so we as DBAs we don't encounter the problem in the first place!

I think you will find the lesson helpful, but take a look at my other tutorials and as always give me a shout or comment if you have any questions.

Like and Subscribe for more videos!

On this page of the site you can watch the video online Delete Duplicate Rows in SQL Table with a duration of hours minute second in good quality, which was uploaded by the user The SQL Guy 03 February 2020, share the link with friends and acquaintances, this video has already been watched 1,485 times on youtube and it was liked by 13 viewers. Enjoy your viewing!

139