Delete all but one duplicate record

ANSI SQL Solution

Use group by in a subquery:

delete from my_tab where id not in 
(select min(id) from my_tab group by profile_id, visitor_id);

You need some kind of unique identifier(here, I'm using id).

MySQL Solution

As pointed out by @JamesPoulson, this causes a syntax error in MySQL; the correct solution is (as shown in James' answer):

delete from `my_tab` where id not in
( SELECT * FROM 
    (select min(id) from `my_tab` group by profile_id, visitor_id) AS temp_tab
);

Here's Frank Schmitt's solution with a small workaround utilizing a temporary table to allow his solution to work on MySQL:

delete from `my_tab` where id not in
( SELECT * FROM 
    (select min(id) from `my_tab` group by profile_id, visitor_id) AS temp_tab
)

This will work:

With NewCTE
AS
(
Select *, Row_number() over(partition by ID order by ID)as RowNumber from 
table_name
)
Delete from NewCTE where RowNumber > 1

Select all unique rows
Copy them to a new temp table
Truncate original table
Copy temp table data to original table

That's what I'd do. I'm not sure if there's 1 query that would do all this for you.

Delete all but one duplicate record

Related

Recent Posts