Removing duplicate rows from MySQL table
So I have this MySQL database setup:
+--------------------+----------+
| email | password |
+--------------------+----------+
| example@gmail.com | xxx |
| example2@yahoo.com | xxx |
| example@gmail.com | xxx |
+--------------------+----------+
I want to remove duplicate lines where email and password are the same.
Any ideas on how to do this?
source to share
As long as you have already populated your spreadsheet and Gordon's answer is for your current situation and perhaps for the fastest solution, to avoid introducing duplicates in the future, you should:
create a unique (composite) index on the column (s) you want to be unique. In your case, your sql will look something like this:
Alter table yourtablename
add unique index idx_unq(`email`,`password`);
Then do INSERT IGNORE INTO
instead INSERT INTO
. This will ignore any future duplicate entries from the checkout to the table.
This post might also help you.
& "INSERT INTO .. ON DUPLICATE KEY UPDATE" Only inserts new records, not replaces?
source to share
Considering you don't have an id column, one way is to create a temporary table and reinstall the data:
create temporary table tempt as
select distinct email, password
from yourtable;
truncate table yourtable
insert into yourtable(email, password)
select email, password
from tempt;
EDIT:
_pala has a good point. You can prevent this problem:
create unique index idx_table_email_password on table(email, password)
I would also advise you to enable the auto-appending primary key. However, the question was about removing duplicates from the existing table, not a better data structure.
source to share