0

My table name is dummy and it contains 6 columns where ID, NAME, TIMESTAMP and other 3 columns.

I want to delete the duplicate rows whose ID, NAME and TIMESTAMP values matches with another rows. If there multiple rows with same ID, NAME and TIMESTAMP values then keep one and delete others.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Kalpataru Mallick
  • 83
  • 1
  • 3
  • 10

2 Answers2

0

Here is the solution

https://stackoverflow.com/a/5016434

Another possible solution that I've just come across:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

if you want to keep the row with the lowest id value OR

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

https://stackoverflow.com/a/5770309

DELETE DupRows.*
FROM MyTable AS DupRows
   INNER JOIN (
      SELECT MIN(ID) AS minId, col1, col2
      FROM MyTable
      GROUP BY col1, col2
      HAVING COUNT(*) > 1
   ) AS SaveRows ON SaveRows.col1 = DupRows.col1 AND SaveRows.col2 = DupRows.col2
      AND SaveRows.minId <> DupRows.ID;
Community
  • 1
  • 1
sravis
  • 3,562
  • 6
  • 36
  • 73
0

Here is an example code. Which will help you to accomplish it.

DELETE duplicate_rows.*
FROM dummy AS duplicate_rows
   INNER JOIN (
      SELECT MIN(id) AS id, name, timestamp
      FROM dummy
      GROUP BY session_id, user_id, category_id
      HAVING COUNT(*) > 1
   ) AS unique_rows 
ON unique_rows.session_id = duplicate_rows.session_id 
AND unique_rows.user_id = duplicate_rows.user_id
AND unique_rows.category_id = duplicate_rows.category_id
AND unique_rows.id <> duplicate_rows.id;

More detailed explanation you find here: http://mildcoder.com/delete-duplicate-rows-from-a-mysql-table/

Salsan Jose
  • 1