How to remove all of the duplicates from the table?

Question

I have the following data in my database

I have got some duplicate records while inserting data .

CREATE TABLE `historical_data` ( `symbol_name` varchar(70) DEFAULT NULL, `current_day` varchar(50) DEFAULT NULL, `open_val` varchar(50) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; Insert Into historical_data values('SBIN','10-DEC-2015','300.10'); Insert Into historical_data values('SBIN','10-DEC-2015','300.10'); Insert Into historical_data values('SBIN','10-DEC-2015','300.10'); Insert Into historical_data values('ACC','10-DEC-2015','1233.10'); Insert Into historical_data values('TATELXSI','10-DEC-2015','1980.10');

Could you please let me know how to remove all the duplicates from the table

Duplicate means same symbol and same current day

http://sqlfiddle.com/#!9/70d96

Duplicates meaning having the same values for all symbol_name,current_day,open_val in the same row, or just a duplicate value within any of these columns? — ʰᵈˑ
– ʰᵈˑ, Commented Dec 11, 2015 at 15:00
@hd having the same values for all symbol_name,current_day,open_val in the same row , for example SBIN has got two duplicate values and i need to remove 2 of them . — Pawan
– Pawan, Commented Dec 11, 2015 at 15:02
You can consider duplicate as same symbol and same current day . — Pawan
– Pawan, Commented Dec 11, 2015 at 15:03

Ankit Bajpai · Accepted Answer · 2015-12-11 15:15:29Z

There is no need to do so. Just run this query as it is:-

ALTER IGNORE TABLE `historical_data` ADD UNIQUE INDEX (`symbol_name`, `current_day`, `open_val` );

This will add unique index to your table and remove all dulicate rows and will make sure that no duplicate rows being inserted in future.

Gaurav Lad · Accepted Answer · 2015-12-11 15:08:10Z

0

You can write down a STORED PROCEDURE to read every row using CURSOR and to run a loop to find out any similar row, and if found, you just need to DELETE that particular row.

answered Dec 11, 2015 at 15:08

Gaurav Lad

1,8081 gold badge17 silver badges31 bronze badges

2 Comments

ʰᵈˑ Over a year ago

Generally, an answer offers the solution and not just hints.

Gaurav Lad Over a year ago

Excellent people needs a guidance, rest are spoon feeders.

Vasfed · Accepted Answer · 2015-12-11 15:17:09Z

You can add a primary key to table, so that you can differentiate duplicates without using cursors and then delete them by joining table onto itself:

ALTER TABLE historical_data ADD COLUMN `id` int auto_increment primary key not null; DELETE from historical_data AS hd LEFT JOIN historical_data hd1 ON hd1.symbol_name=hd.symbol_name AND hd1.current_day=hd.current_day AND hd1.id < hd.id WHERE hd1.id is NOT NULL

or use a unique index:

ALTER IGNORE TABLE historical_data ADD UNIQUE INDEX unique_index_on_symbol_name_and_day (symbol_name,current_day);

Collectives™ on Stack Overflow

How to remove all of the duplicates from the table?

3 Answers 3

Comments

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Related