Remove identical records in mySQL

Question

I have columns ID, A, and B. in Table1

ID = Autonumber, Primary key

A = Foreign key

B = Foreign key

This table is for many to many and let's ignore column ID for now. I need to remove duplicates from my many to many.

example:

+----+---+
| A  | B |
+----+---+
| 1  | 1 |
| 1  | 2 |
| 1  | 1 |
| 1  | 2 |
| 1  | 3 |
| 2  | 1 |
+----+---+

Turn it into

+----+---+
| A  | B |
+----+---+
| 1  | 1 |
| 1  | 2 |
| 1  | 3 |
| 2  | 1 |
+----+---+

removing those unnecessary duplicates. can you help me with the query for MySQL, please?

Answer 1

For a table of any size, it is often faster to truncate the table and re-insert records then to do a massive delete. This is particularly true when the table in question has no primary key.

Hence, I would recommend something like this:

create table temp_table1 as
    select distinct a, b
    from table1;

truncate table table1;

insert into table1(a, b)
    select a, b
    from table1;

Of course, make a copy of table1 before doing anything -- just in case.