MySql: check and remove duplicate rows

Question

How can I check if a table contains duplicate rows, with the same value of the couple (date, time) ?
How can I remove all but one row ?
Id is a sequential number that I increment after every post, how can I restore it after removing a row ?

the table tableTen is:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| symbol    | varchar(25) | YES  |     | NULL    |       |
| date      | date        | YES  | MUL | NULL    |       |
| time      | time        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

Priyanka Kariya · Accepted Answer · 2016-11-16 05:33:02Z

1. How can I check if a table contains duplicate rows, with the same value of the couple (date, time) ?

SELECT *  FROM db.table HAVING COUNT(symbol) > 1

2. How can I remove all but one row ?

a) If you want to keep the row with the lowest id value:

DELETE n1 FROM table n1, table n2 WHERE n1.id > n2.id AND n1.symbol= n2.symbol

b) If you want to keep the row with the highest id value:

DELETE n1 FROM table n1, table n2 WHERE n1.id < n2.id AND n1.symbol= n2.symbol

3. Id is a sequential number that I increment after every post, how can I restore it after removing a row ?

Please try http://befused.com/mysql/reset-auto-increment

Rick James · Accepted Answer · 2016-11-16 20:43:51Z

Your specification is unclear -- Do you want distinct values for the triple (symbol, date, time)? Or just of (symbol)? I will assume the former since the latter gets messier.

If there are a large number of dups, then this may be faster.

CREATE TABLE new LIKE tableTen;
INSERT INTO new
    SELECT DISTINCT MAX(id), symbol, date, time FROM tableTen
        GROUP BY             symbol, date, time;
RENAME TABLE tableTen TO old,
             new TO tableTen;
DROP TABLE old;

Caveat: If you have NULLs, it may not work as expected.

Side issues:

Your lack of a PRIMARY KEY is a no-no.
Having all columns NULLable is rarely realistic.
Splitting date and time into two separate columns usually leads to clumsy coding.

Stack Exchange Network

MySql: check and remove duplicate rows

2 Answers 2

Your Answer

Hot Network Questions

MySql: check and remove duplicate rows

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions