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 | |
+-----------+-------------+------+-----+---------+-------+

Answer 1

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

Answer 2

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.