CODEWITHSUNDEEP
mysqlsql
ing1408
ing1408

Reputation: 1

MySQL: How to remove duplicate records or check for duplicate when insert?

I know there are many answers indicated using rank() or row_number(), however, they are not supported in SQL-FRONT or Navicat, I tried Microsoft SSMS but it didnot support my sql. I tried many methods, didn't work. So I'm hopeless now.

Table is shown below, I just want to know how to remove duplicate records with the same name and time. Thank you.

a simple table shown in the link

Upvotes: 0

Views: 145

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

In MySQL, you can use join and aggregation:

delete t
     from t join
          (select name, time, min(id) as min_id
           from t
           group by name, time
          ) tt
          on t.name = tt.name and t.time = tt.time and
             t.id > tt.min_id;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

Use an EXISTS clause to find duplicates.

delete from mytable
where exists
(
  select *
  from mytable other
  where other.name = mytable.name
  and other.time = mytable.time
  and other.id < mytable.id
);

As for inserts: Place a unique constraint on the two columns. This is usually done with a unique index:

create unique index idx_unique_name_time on mytable (name, time);

Upvotes: 1

Related Questions