0

how to delete all duplicate row on mysql and keep one with Query?

table name is: transaction

my duplicate row:

reference    type  
---------  --------
        1         9
        1         10
        1         10
        1         10
        1         11
        1         11

correct row:

reference    type  
---------  --------
        1         9
        1         10
        1         11
zhorachu
  • 1
  • 3

1 Answers1

1
ALTER IGNORE TABLE `transaction`   
ADD UNIQUE INDEX (`col_1`, `col_2`);

ALTER : is used to modify tables.

IGNORE: if the IGNORE is in use, only the first row of the duplicated rows is used. So the rest of rows are deleted.

ADD UNIQUE INDEX: In order to prevent duplicates, we add unique indexes in the current column separately. Unique indexes work in a same way like primary keys. Although you can have only one primary key, unique indexes you can have as many as your number of columns, in that way you can raise your safe about the uniqueness of the current column without duplicates.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nikos Takiris
  • 149
  • 1
  • 13
  • please add some description with your answer – Muhammad Omer Aslam Dec 04 '17 at 00:45
  • Muhammad Omer: it creates a unique index on the column, basically eliminating duplicates. It would call an error but the ignore overrides that. its basically saving one entry and ignoring/removing the rest by making it unique. Dirty but very effective!! – Fstarocka Burns Jan 12 '18 at 02:48