how to Delete Duplicate Rows but keeping 1 based on two columns

Question

I have table called scheduler. It contains following columns:

ID
sequence_id
schedule_time (timestamp)
processed
source_order

I need to delete duplicate rows from the table but keeping 1 row which has same schedule_time and source_order for a particular sequence_id where processed=0

A very simple solution would be to `delete from table where ID not in (select ID's of distinct records )` — Techmonk, Feb 08 '13 at 06:52

score 1 · Accepted Answer · edited May 23 '17 at 12:16

1

DELETE yourTable FROM yourTable LEFT OUTER JOIN (
SELECT MIN(ID) AS minID FROM yourTable WHERE processed = 0 GROUP BY schedule_time, source_order
) AS keepRowTable ON yourTable.ID = keepRowTable.minID
WHERE keepRowTable.ID IS NULL AND processed = 0

I apply from this post ;P How can I remove duplicate rows? Have you seen it?

--fixed version--

DELETE yourTable FROM yourTable LEFT OUTER JOIN (
SELECT MIN(ID) AS minID FROM yourTable WHERE processed = 0 GROUP BY schedule_time, source_order
) AS keepRowTable ON yourTable.ID = keepRowTable.minID
WHERE keepRowTable.minID IS NULL AND processed = 0

edited May 23 '17 at 12:16

Community

1
1

answered Feb 08 '13 at 09:41

ittgung

134
1
2
6

thanks, but a slight mistake keepRowTable.minID instead of keepRowTable.ID – Shishir Mhamal Feb 08 '13 at 10:35
Thank you for your correction. Sorry for my mistake. I have fixed it as your suggestion. – ittgung Feb 11 '13 at 03:45

score 0 · Answer 2 · answered Feb 08 '13 at 06:51

0

For mysql

DELETE a from  tbl a , tbl b WHERE a.Id>b.Id and
a.sequence_id= b.sequence_id and a.processed=0;

answered Feb 08 '13 at 06:51

Meherzad

8,433
1
30
40

the query is taking lots of time to execute. i have 3000 rows – Shishir Mhamal Feb 08 '13 at 07:28
Try this http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql – Meherzad Feb 08 '13 at 07:34
its taking too long to execute. – Shishir Mhamal Feb 08 '13 at 08:06

Bingo · Answer 3 · 2013-02-09T06:13:00.717

The fastest way to remove duplicates - is definitely to force them out by adding an index, leaving only one copy of each left in the table:

ALTER IGNORE TABLE dates ADD PRIMARY KEY (

  ID
  sequence_id
  schedule_time
  processed
  source_order
)

Now if you have a key, you might need to delete it and so on, but the point is that when you add a unique key with IGNORE to a table with duplicates - the bahavior is to delete all the extra records / duplicates. So after you added this key, you now just need to delete it again to be able to make new duplicates :-)

Now if you need to do more complex filtering (on witch one of the duplicates to keep that you can not just include in indexes - although unlikely), you can create a table at the same time as you select and input what you want in it - all in the same query:

CREATE TABLE tmp SELECT ..fields..  GROUP BY ( ..what you need..)

DROP TABLE original_table

ALTER TABLE tmp RENAME TO original_table_name

how to Delete Duplicate Rows but keeping 1 based on two columns

3 Answers3