0

Let's say I have a Mysql table that contains timestamps, ids, and a few more parameters. How can I remove an entire row based on duplicate timestamps?

SELECT DISTINCT COL_NAME From tblname, will return 1 column with my timestamps filtered, but I need to actually remove them from my table (The Entire row).

Thanks!

ahmed
  • 9,071
  • 3
  • 9
  • 22
Dan Nir
  • 45
  • 5
  • do you need to delete all rows or all but 1 having duplicates? – Rinkesh P May 07 '23 at 06:18
  • @RinkeshP The Final result should contain only 1 row by the 2 rows that are duplicate – Dan Nir May 07 '23 at 06:32
  • Does this answer your question? [Delete all Duplicate Rows except for One in MySQL?](https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql) – SelVazi May 07 '23 at 09:42
  • 1
    Try this : Delete from table_name where timestamps IN(Select timestamps from table_name group by timestamps having count(*)>1) .This will delete all rows from table where timestamps value is duplicate. – havin May 07 '23 at 11:40

1 Answers1

0

According to my opinion first find the Duplicate timestamps data rows. After find the duplicate rows , you can delete one of the duplicate row. If you like to delete smallest id for each group of rows with the same timestamp value this is my suggest code. This is code is combination of subqueries.

  CREATE TEMPORARY TABLE tmp_table AS ( // create temp table
  SELECT MIN(id) AS id_to_keep
  FROM tblname
  GROUP BY timestamp_col
  HAVING COUNT(*) > 1
);

DELETE FROM tblname
WHERE id NOT IN (SELECT id_to_keep FROM tmp_table);
Free Helper
  • 26
  • 4
  • thanks for the answer! I notice that while running NOT IN it will return all the DELETED row and not the Original Table without the deleted row , but while selecting it , its working perfectly fine , but when I Try to delete IM Getting : `Error Code: 1093. You can't specify target table tblname' for update in FROM clause` – Dan Nir May 07 '23 at 07:49
  • `SELECT * FROM tblname WHERE id IN ( SELECT MIN(id) FROM tblname GROUP BY timestamp HAVING COUNT(*) > 1 )` - This Work Perfect – Dan Nir May 07 '23 at 07:50
  • `delete FROM tblname WHERE id IN ( SELECT MIN(id) FROM tblname GROUP BY timestamp HAVING COUNT(*) > 1 )` `-- this returns Error code 1093` – Dan Nir May 07 '23 at 07:51
  • 1
    Sometimes try to modify same table cause to get error in mysql using NOT IN with a subquery, So you can create temporary table or a subquery to create a list of the id values to delete. After that you can seperate delete rows. I updated my answer according that.Check my answer – Free Helper May 07 '23 at 08:37