remove duplicate entry in SQL database with null fields

Question

how can i remove duplicate entry in SQL database when there are some null fields: i have a table Mesure2 that contains duplicate entries, some having null fields, i have removed the other duplicate entries like this:

INSERT Mesures SELECT distinct * FROM Mesures2;

but there is still duplicate entries with null field that prevent me from creating Keys:

MariaDB [sidonie2]> ALTER TABLE Mesures ADD PRIMARY KEY (`N° Fiche`,Date,Angle,Sépar,`Nb Nuits`,CodeObs,Instrument,dimension,Réf,Nota);
 ERROR 1062 (23000): Duplicate entry '2928-1892.93-258.6-03.34-2-JNS---LDS-' for key 'PRIMARY'

MariaDB [sidonie2]> select * from Mesures where `N° Fiche` = 2928 and Date = 1892.93;
  +-----------+---------+-------+--------+----------+---------+-----------+------------+------+------+
  | N° Fiche  | Date    | Angle | Sépar  | Nb Nuits | CodeObs | dimension | Instrument | Réf  | Nota |
  +-----------+---------+-------+--------+----------+---------+-----------+-----------+------+------+
  |      2928 | 1892.93 | 258.6 | 03.34  |        2 | JNS     | NULL      | NULL       | LDS  |      |
  |      2928 | 1892.93 | 258.6 | 03.34  |        2 | JNS     | NULL      | NULL       | LDS  | NULL |
  +-----------+---------+-------+--------+----------+---------+-----------+------------+------+------+

how can i eliminate duplicate that differs only by a Null and empty same field (here it is Nota field but could be any one else)?

score 1 · Answer 1 · answered Dec 22 '16 at 15:07

It depends if you want to preserve the nulls as NULL:

INSERT Mesures (`N° Fiche`,Date,Angle,Sépar,`Nb Nuits`,CodeObs,Instrument,dimension,Réf,Nota)
SELECT DISTINCT
    `N° Fiche`,
    Date,
    Angle,
    Sépar,
    `Nb Nuits`,
    CodeObs,
    Instrument,
    dimension,
    Réf,
    nullif(Nota,'')
FROM Mesures2;

Or if you want the nulls to be empty strings:

INSERT Mesures (`N° Fiche`,Date,Angle,Sépar,`Nb Nuits`,CodeObs,Instrument,dimension,Réf,Nota)
SELECT DISTINCT
    `N° Fiche`,
    Date,
    Angle,
    Sépar,
    `Nb Nuits`,
    CodeObs,
    Instrument,
    dimension,
    Réf,
    ifnull(Nota,'')
FROM Mesures2;

Note that nullif() and ifnull() are very different functions.

Thank you for your help, i use ifnull() (or nullif) on fields. — Damien Mattei, Jan 03 '17 at 13:56

score 0 · Answer 2 · answered Dec 22 '16 at 14:56

Very strange that you would define an entire row as a primary key. Have you considered an auto-increment field?

In any case, if you want to remove NULL values, do it in the insert:

INSERT Mesures(`N° Fiche`, Date, Angle, Sépar, `Nb Nuits`, CodeObs, Instrument, dimension, Réf, Nota)
    SELECT distinct `N° Fiche`, Date, Angle, Sépar, `Nb Nuits`, CodeObs, Instrument, dimension, Réf, Nota
    FROM Mesures2
    WHERE `N° Fiche` IS NOT NULL AND
          Date IS NOT NULL AND
          Angle IS NOT NULL AND
          Sépar IS NOT NULL AND
          `Nb Nuits` IS NOT NULL AND
          CodeObs IS NOT NULL AND
          Instrument IS NOT NULL AND
          dimension IS NOT NULL AND
          Réf IS NOT NULL AND
          Nota IS NOT NULL;

This approach does assume that the table is empty before the INSERT.

about the primary key,the table was not done be me and i trying to change this,about the answer if i do that suppose i will get only the line having no null field,but i must keep all the distinct lines unless they differ by having one or more null field which duplicate with an empty field — Damien Mattei, Dec 28 '16 at 20:57
no it's not what i want, this query will select and insert the distinct data that have no NULL field but that will remove valid data only because they have a NULL field, not good. — Damien Mattei, Jan 03 '17 at 13:32

score -1 · Answer 3 · edited Aug 20 '18 at 06:23

-1

alter IGNORE TABLE tablename add UNIQUE index(col_name)

edited Aug 20 '18 at 06:23

Kamil Gosciminski

16,547
8
49
72

answered Aug 20 '18 at 06:21

Mohit Chauhan

78
2
7

2

Are you sure that this removes any row in the database? I don't think so – Nico Haase Aug 20 '18 at 07:09
At one point, this was possible with MySQL, see: https://stackoverflow.com/questions/5456520/mysql-alter-ignore-table-add-unique-what-will-be-truncated – Andrioid Aug 20 '18 at 09:32

remove duplicate entry in SQL database with null fields

3 Answers3