1
  • 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    |       |
+-----------+-------------+------+-----+---------+-------+
4

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

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.