I have a huge table of products but there are lot of duplicate entries. The table has more than10 Thousand entries and I want to remove the duplicate entries in it without manually finding and deleting it. Please let me know if you can pr开发者_运维问答ovide me a solution for this

You could use SELECT DISTINCT INTO TempTable, drop the original table, and then rename the temp one.

You should also add primary and unique keys to avoid this sort of thing in the future.

for full row duplicates try this.

select distinct * into mytable_tmp from mytable
drop table mytable
alter table mytable_tmp rename mytable

Seems the below statements will help you in resolving your requirements.

if the table(foo) has primary key field

First step

store key values in temporary table, give your unique conditions in group by clause if you want to delete the duplicate email id, give email id in group by clause and give the primary key name in select clause like either min(primarykey) or max(primarykey)

CREATE TEMPORARY TABLE temptable AS SELECT min( primarykey ) FROM foo GROUP BY uniquefields;

Second step

call the below delete statement and give the table name and primarykey columns

DELETE FROM foo WHERE primarykey NOT IN (SELECT * FROM temptable );

execute both the query combined in your query analyser or db tool.

If the table(foo) doesn't have a primary key filed

step 1

CREATE TABLE temp_table AS SELECT * FROM foo GROUP BY field or fileds;

step 2

DELETE FROM foo;

step 3

INSERT INTO foo select * from temp_table;

There are different solutions to remove duplicate rows and it fully depends upon your scenario to make use of one from them. The simplest method is to alter the table making the Unique Index on Product Name field:

alter ignore table products add unique index `unique_index` (product_name);

You can remove the index after getting all the duplicate rows deleted:

alter table products drop index `unique_index`;

Please let me know if this resolves the issue. If not I can give you alternate solutions for that.

You can add more than one column to a group by. I.E.

SELECT * from tableName GROUP BY prod_name HAVING count(prod_name) > 1

That will show the unique products. You can write it dump it to new table and drop the existing one.