CODEWITHSUNDEEP
mysql
Pawan
Pawan

Reputation: 32321

How to remove all of the duplicates from the table?

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

Upvotes: 0

Views: 86

Answers (3)

Vasfed
Vasfed

Reputation: 18444

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);

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

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.

Upvotes: 4

Gaurav Lad
Gaurav Lad

Reputation: 1808

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.

Upvotes: 0

Related Questions