By James P.


2011-05-04 11:30:09 8 Comments

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   

4 comments

@Vik Wilder 2017-12-21 11:14:59

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

@Frank Schmitt 2011-05-04 11:34:47

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

UPDATE

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

@James P. 2011-05-04 11:58:34

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

@Desty 2016-03-03 13:44:46

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

@VipinS 2016-05-21 08:37:19

same error is here . its not working

@Frank Schmitt 2016-05-29 13:12:45

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.

@user3467349 2016-12-23 12:14:21

What if there is no id key?

@James P. 2011-05-04 12:02:10

Here's Frank Schmitt's solution with a small workaround for the temporary table:

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
)

@James P. 2016-05-30 06:43:46

@FrankSchmitt it's perfectly fine :)

@gmadd 2011-05-04 11:35:26

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.

@James P. 2011-05-04 12:03:10

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.

Related Questions

Sponsored Content

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2570375 View
  • 1735 Score
  • 29 Answer
  • Tags:   sql duplicates

25 Answered Questions

[SOLVED] What is the difference between UNION and UNION ALL?

  • 2008-09-08 15:19:33
  • Brian G
  • 1253079 View
  • 1319 Score
  • 25 Answer
  • Tags:   sql union union-all

20 Answered Questions

[SOLVED] How to delete duplicate rows in SQL Server?

24 Answered Questions

[SOLVED] Retrieving the last record in each group - MySQL

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

20 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2085561 View
  • 1053 Score
  • 20 Answer
  • Tags:   sql oracle

19 Answered Questions

24 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

1 Answered Questions

[SOLVED] desc table in mysql say Null is No but default is NULL?

2 Answered Questions

[SOLVED] Delete all Duplicate Rows except for One in MySQL?

  • 2011-01-13 20:58:38
  • Highway of Life
  • 434486 View
  • 401 Score
  • 2 Answer
  • Tags:   mysql sql duplicates

Sponsored Content