Innodb regrouper 2 lignes, fusionner 2 entrées, avec foreign key

[mokadjo]

Bonjour,
J'ai pas mal cherché de la doc là-dessus, mais impossible de trouver, et d'ailleurs, c'est très compliqué de trouver quoi chercher ... je vous explique :
dans ma bdd, j'ai une première table qui liste des contacts (id, nom, prénom), une deuxième table qui liste des livres (id, titre), et une troisième "livres_auteurs" qui lit les 2 (id, livre, contact).
Ces 3 tables sont en InnoDB, "id" de la table "contacts" et "id" de la table "livres" sont liés en foreign key dans la table "livres_auteurs" avec des "ON UPDATE CASCADE" et "ON DELETE CASCADE"... jusque là tout va bien

Mon problème c'est que dans ma table "contacts" il y a des personnes qui ont été entré 2 fois (voire plus). J'aimerai réunir les lignes dupliquées en une seule, et que ça se répercute sur toutes les autres tables. Je vais essayer de préciser un peu :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
==contacts==
id - nom - prénom
3 - jean - Dupont
4 - marc - Meunier
5 - paul - Dupond
6 - Jean - dupont
=========
 
==livres_auteurs==
id - livre - auteur
1 --- 5 ---- 3
2 --- 9 ---- 3
3 --- 1 ---- 5
4 -- 10 ---- 6
=============

Pour les lignes 3 et 6 de "contacts", c'est la même personne. Les livres 1, 2 et 4 ont été écris par la même personne.

Donc, comment faire pour réunir les contacts 3 et 6, et que la modification en cascade fonctionne ?

Résultat désiré :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
==contacts==
id - nom - prénom
3 - jean - Dupont
4 - marc - Meunier
5 - paul - Dupond
(6 - Jean - dupont => supprimé)
=========
 
==livres_auteurs==
id - livre - auteur
1 --- 5 ---- 3
2 --- 9 ---- 3
3 --- 1 ---- 5
4 -- 10 ---- 3
=============

Je vous remercie d'avance !!!!!

[CinePhil]

Pour commencer, puisque la table livres_auteurs est une table associative, elle ne devrait pas avoir son propre id ; sa clé primaire devrait être composée des clés étrangères référençant les identifiants des deux autres tables :
livres_auteurs (livre, contact) serait suffisant.

Et pendant que j'y suis, on nomme en principe les tables au singulier, par au pluriel.

Passons au problème...

1) En supposant qu'il n'y a pas deux contacts physiquement différents mais homonymes, cherchons les doublons :

Code :

1
2
3
4
SELECT nom, prenom
FROM contacts
GROUP BY nom, prenom
HAVING COUNT(*) > 1

=> Résultat :
nom / prenom
Dupont / Jean


2) Puis cherchons l'id mini de ces doublons :

Code :

1
2
3
4
SELECT nom, prenom, MIN(id) AS id_mini
FROM contacts
GROUP BY nom, prenom
HAVING COUNT(*) > 1

=> Résultat :
nom / prenom / id_mini
Dupont / Jean / 3

3) Cherchons maintenant les livres écris par les contacts en double :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT la.livre, la.contact, c.nom, c.prenom, 
    tmp.id_mini AS nouveau_contact
FROM livres_auteurs la
INNER JOIN contacts c ON c.id = la.contact
    INNER JOIN
    (
        SELECT nom, prenom, MIN(id) AS id_mini
        FROM contacts
        GROUP BY nom, prenom
        HAVING COUNT(*) > 1
    ) tmp
        ON tmp.nom = c.nom
        AND tmp.prenom = c.prenom
ORDER BY c.nom, c.prenom, la.contact, la.livre

=> Résultat :
livre / nom / prenom / contact / nouveau_contact
5 / Dupont / Jean / 3 / 3
9 / Dupont / Jean / 3 / 3
10 / Dupont / Jean / 10 / 3

4) Mettons la requête précédente dans une table temporaire et faisons une jointure avec la table livres_auteurs pour la mettre à jour :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TEMPORARY TABLE modif
SELECT la.livre, la.contact, c.nom, c.prenom, 
    tmp.id_mini AS nouveau_contact
FROM livres_auteurs la
INNER JOIN contacts c ON c.id = la.contact
    INNER JOIN
    (
        SELECT nom, prenom, MIN(id) AS id_mini
        FROM contacts
        GROUP BY nom, prenom
        HAVING COUNT(*) > 1
    ) tmp
        ON tmp.nom = c.nom
        AND tmp.prenom = c.prenom
ORDER BY c.nom, c.prenom, la.contact, la.livre;
 
UPDATE livres_auteurs la
INNER JOIN modif m ON m.contact = la.contact
SET la.contact = m.nouveau_contact
WHERE m.contact <> m.nouveau_contact

À essayer sur un petit jeu de données. Si le volume de données à traiter est important, on peut insérer une indexation après la création de la table temporaire pour accélérer la mise à jour :

Code :

1
2
3
ALTER TABLE modif
ADD INDEX(nouveau_contact),
ADD INDEX(contact);

5) Dans la foulée, on peut supprimer les contacts en double ; ils figurent dans la table temporaire :

Code :

1
2
3
DELETE FROM contact c
INNER JOIN modif m ON m.contact = c.contact
WHERE m.contact <> m.nouveau_contact

Ce qui donnerait le petit programme complet suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TEMPORARY TABLE modif
SELECT la.livre, la.contact, c.nom, c.prenom, 
    tmp.id_mini AS nouveau_contact
FROM livres_auteurs la
INNER JOIN contacts c ON c.id = la.contact
    INNER JOIN
    (
        SELECT nom, prenom, MIN(id) AS id_mini
        FROM contacts
        GROUP BY nom, prenom
        HAVING COUNT(*) > 1
    ) tmp
        ON tmp.nom = c.nom
        AND tmp.prenom = c.prenom
ORDER BY c.nom, c.prenom, la.contact, la.livre;
 
ALTER TABLE modif
ADD INDEX(nouveau_contact),
ADD INDEX(contact);
 
UPDATE livres_auteurs la
INNER JOIN modif m ON m.contact = la.contact
SET la.contact = m.nouveau_contact
WHERE m.contact <> m.nouveau_contact;
 
DELETE FROM contact c
INNER JOIN modif m ON m.contact = c.contact
WHERE m.contact <> m.nouveau_contact;

[Yanika_bzh]

Attention a bien regrouper par UPCASE des nom et prenom dans le 1) de Cinephil ...

La remarque du 1) de Cinephil est tres tres importante. Si vous automatisez une modification des doublons, il faut s'assurer que ce sont biens des doublons ... et pas une simple homonymie.

Bon courage

[mokadjo]

Alors, déjà un grand merci pour vos réponses !!!
Je pense ne pas avoir assez insisté sur certains points :

- Mes 2 auteurs sont bien les 2 mêmes personnes physique, je donnais un exemple simple, avec des livres et des auteurs, pour ne pas rentrer dans une explication complexe.

- L'autre point sur lequel je n'ai pas assez insisté, c'est sur le fait qu'il y a une multitude de tables relationnelles liées à l'auteur... on a qu'à dire qu'il y a une table livre_illustrateur, livre_traducteur, livre_editeur, livre_vendeur, livre_distributeur, livre_droits_fiche, auteur_membre, auteur_image, auteur_societe, etc.... des tonnes de tables qui sont liées avec la table auteur. Dans ce cas, si je veux réunir 2 auteurs en un seul, je veux pas avoir à faire une requête longue comme le bras qui va modifier l'id de l'auteur dans chaque table relationnelle, j'imagine qu'il doit exister une requête pour dire "tous les champs liés à l'index 10 se transforment en index 3"... vous voyez ce que je veux dire ? Et en plus, dès l'instant que je vais rajouter une table relationnelle, va falloir que je rajoute ça dans mon script... enfin s'il n'y a pas d'autre solution j'vais faire comme ça, mais j'imagine qu'il y a une alternative non ?

[CinePhil]

Ben non ! Si tu n'as qu'un doublon à corriger, tu peux faire de simples requêtes UPDATE sur chaque table associative mais sinon la méthode que j'ai donnée est la bonne pour automatiser le processus. J'ai donné le principe, il est identique pour toutes les tables associatives. Prend quand même la précaution de supprimer les tables temporaires au fur et à mesure :

Code :

DROP TABLE modif;

Bien entendu, il ne faut remplacer la requête DELETE pour supprimer les doublons dans la table contacts.

Une fois que tu auras fait tout ça, ajoute un index UNIQUE sur le couple {nom, prenom} ; ça t'éviteras de devoir recommencer dans quelques temps !