Delete all but one duplicate record

Question

I have a table that is supposed to keep a trace of visitors to a given profile (user id to user id pair). It turns out my SQL query was a bit off and is producing multiple pairs instead of single ones as intended. With hindsight I should have enforced a unique constraint on each id+id pair.

Now, how could I go about cleaning up the table? What I want to do is delete all duplicate pairs and leave just one.

So for example change this:

23515 -> 52525 date_visited
23515 -> 52525 date_visited
23515 -> 52525 date_visited
12345 -> 54321 date_visited
12345 -> 54321 date_visited
12345 -> 54321 date_visited
12345 -> 54321 date_visited
23515 -> 52525 date_visited
...

Into this:

23515 -> 52525 date_visited
12345 -> 54321 date_visited

Update: Here is the table structure as requested:

id  int(10)         UNSIGNED    Non     Aucun   AUTO_INCREMENT
profile_id  int(10)         UNSIGNED    Non     0 
visitor_id  int(10)         UNSIGNED    Non     0
date_visited    timestamp           Non     CURRENT_TIMESTAMP   

Lucila

What is the table structure please? is there a 3rd column to tie-break values? – Tavi

@gbn: The table structure has been added (MySQL). The third column is to keep a trace of the last time a user visited a profile. The structure should probably be modified with a constraint on profile_id & visitor_id. P.S: I don't have the SQL populating the table right now but it's something along the lines of if exists update timestamp if not create record. – Lucila

Answer 1

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
);

Hsining

Great solution. I hadn't thought of using a group by (experience>knowledge). This displays a Can't specify target in FROM clause but there's a workaround for this (see my answer). – Lucila

Note, this doesn't work in MySQL because it doesn't allow you to modify the table you're using in the inner select: Error Code: 1093. You can't specify target table 'my_tab' for update in FROM clause – Uvulitis

I've updated the answer; I originally thought that people would read the comment / answer by @JamesPoulson and use their version, but apparently, that's not always the case. – Hsining

What if there is no id key? – Yang

Very elegant solution. Sometimes better use MAX instead of MIN, so you'll keep latest version of rows which are probably most correct. – Hudson

This doesn't work in postgres if your id field is a uuid, because there is no min function for uuids – Southerly

Answer 2

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
)

Lucila

@FrankSchmitt it's perfectly fine :) – Lucila

Answer 3

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

Sigler

This answer is the best if you do not have a unique identifier in your table and don't want to create a temporary table. – Ediva

Answer 4

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.

Merow

Using a temporary table is a good reflex and is actually necessary. It's probably a more adapted approach if there's a lot of data. – Lucila

Answer 5

None of the above answers works..... the requirement is to delete all the duplicates and except one from each set.. but based on more than one column data..

try this,

 SET SERVEROUTPUT ON
 declare
 name integer := 1;
 begin    
 
    for test in (
        select tablecolumn1 , tablecolumn2, tablecolumn3, count(1) from 
        yourtable group by tablecolumn1, tablecolumn2, tablecolumn3 
        having count(1) > 1
    )
    loop
           if name <= 1000 then -- for incremental update
           
                 name := name+1;
            
                 delete from test b where 
                 b.tablecolumn1 = test.tablecolumn1  
                 and  b.tablecolumn2 = test.tablecolumn2
                 and  b.tablecolumn3 = test.tablecolumn3
                 and  rownum = 1;            
             
            end if;
    end loop;
    
    DBMS_OUTPUT.PUT_LINE(name);
     
 end;

Candleberry

Answer 6

If you are using SQL you can manually delete the duplicate rows keeping one entry just follow this procedure:

1. Go into your table where you have duplicate data.
2. Apply the filter to segregate duplicate data for each individual id
3. Select all the rows you want to delete.
4. Press delete and save the result.
5. Repeat the process for each id you have duplicate entries for.

It's a long procedure but you can see the results immediately in real-time.

Hope this solution worked for you!!

Muir

Lots of people that utilize these answers are working with millions (or even billions) of rows. It would take them weeks to do this manually. – Wensleydale

What In the world – Stomatal