Get duplicate SQL entities and remove duplicates

We have a table with columns Id (GUID), Title (nvarchar(50)), PubDate (DateTime). After several tests there were inserted a lot of records and many of them have been inserted more than 1 time. The task is to 'seek and destroy' all the duplicate elements and to leave only unique rows.

The solution is connected with GROUP and HAVING. It's easy 🙂

Select query to find duplucates:

select Max(Id) , [Title], [PubDate] , COUNT(Title) from MyTable
GROUP by Title, PubDate
having COUNT(Title) > 1

select Max(Id) , [Title], [PubDate] , COUNT(Title) from MyTable

GROUP by Title, PubDate

having COUNT(Title) > 1

Query to remove duplicates and only the one record must be saved

delete from MyTable where id IN (
	select Max(Id) as Id from MyTable
	GROUP by Title, [PubDate]
	having COUNT(Title) > 1
 )

delete from MyTable where id IN (

select Max(Id) as Id from MyTable

GROUP by Title, [PubDate]

having COUNT(Title) > 1

)

5 May 2023, admin
SQL
Comments: (0)

Get duplicate SQL entities and remove duplicates

No Comments »

Leave a comment

New posts