1
votes

Comment puis-je normaliser des tables MySQL avec des valeurs en double

J'ai 2 tables de fruits et de couleurs.

Dans la table des fruits, la colonne cid fait référence au c_id de la table des couleurs, mais le problème est que la table des couleurs a des noms de couleurs en double:

Existe-t-il un moyen efficace dans MySQL de supprimer les lignes de couleur en double et de mettre à jour le cid dans la table des aliments en conséquence afin que le résultat soit quelque chose comme ça?

sql mysql database

1 commentaires

Vous pouvez simplement exécuter une requête pour chaque couleur que vous supprimez. UPDATE fruits SET cid = '1' WHERE cid = '7' , puis supprimez le c_id 7 du tableau. Répétez pour chaque couleur jusqu'à ce qu'il n'y ait plus de doublons, puis ne laissez plus les doublons (rendez c_name unique). Si c'était moi, j'automatiserais cela en utilisant PHP (mon expérience) ou un autre langage, ce serait assez trivial.

4 Réponses :

1
votes

Vous pourriez vous procurer un ensemble de résultats contenant le cid minimum de couleurs correspondantes pour chaque f_id

SELECT color.*
FROM color
  LEFT OUTER JOIN fruit on color.c_id = fruit.cid
WHERE fruit.f_id IS NULL

Ce n'est pas le plus requête efficace, mais cela fonctionnera. Vous pouvez l'utiliser pour définir correctement votre table de fruits afin qu'elle ne fasse référence qu'à une seule couleur lorsqu'il y a des doublons.

Après avoir corrigé votre table de fruits , vous pouvez ensuite lancer une requête pour voir quelles couleurs sont inutilisées afin que vous sachiez quoi supprimer:

SELECT fruit.f_id, fruit.f_name, min(c2.c_id) as c_id
FROM
    fruit
    INNER JOIN color c1 ON fruit.cid = c1.c_id
    INNER JOIN color c2 ON cl.c_name = c2.c_name
GROUP BY fruit.f_id, fruit.f_name

2 commentaires

Je pense que vous vouliez rejoindre c1 et c2 sur c_name

@Uueerdo Bien sûr. Fixé. Yikes.

0
votes

Vous pouvez y parvenir par étapes -

1. Supprimez les doublons-

UPDATE colors C1
JOIN
(
    SELECT @rownum:=@rownum+1 rownum, c_id, c_name
    FROM colors
    CROSS JOIN (select @rownum := 0) rn
) AS C2 ON C1.c_name = C2.c_name
SET C1.c_id = C2.rownum

2. Réinitialisez le c_id-

DELETE FROM colors C1
WHERE EXISTS (SELECT 1
              FROM colors C2
              WHERE C2.c_name = C1.c_name
              AND C2.c_id > C1.c_id);

1 commentaires

Si fruit.cid a une contrainte de clé étrangère avec une cascade de suppression, cela finira par effacer beaucoup de données sur les fruits; s'il n'y a aucune contrainte du tout, des informations telles qu'une banane jaune devront être reproduites manuellement.

3
votes

En supposant qu'il existe une contrainte de clé étrangère entre les tables, vous devez d'abord mettre à jour table fruit . Pour cela, vous pouvez joindre les tables pour obtenir le nom de la couleur, puis récupérer le c_id minimum de cette couleur à l'aide d'une sous-requête corrélée:

delete c
from color c
inner join color c1 on c1.c_name = c.c_name and c1.c_id < c.c_id

Ensuite, vous pouvez supprimer en toute sécurité les couleurs en double tout en conservant celle avec le c_id le plus bas :

update fruit f
inner join color c on f.cid = c.c_id
set f.cid = (select min(c_id) from color c1 where c1.name = c.c_name)

p >

4 commentaires

Merci! La requête de mise à jour a très bien fonctionné, mais la requête de suppression renvoie une erreur: "c" n'est pas valide à cette position, en attendant: EOF, ':'

@Csaba: J'ai mis à jour la requête de suppression, veuillez me faire savoir si cela fonctionne mieux maintenant.

Maintenant, le code semble valide, mais lorsque j'exécute le code, j'obtiens: "La table 'c' est spécifiée deux fois, à la fois comme cible pour 'DELETE' et comme source séparée pour les données"

@Csaba: ok, je l'ai changé en une requête JOIN ed. Je l'ai testé et cela semble fonctionner correctement.

1
votes

Tout d'abord, vous devez mettre à jour fruit pour ne référencer qu'un seul de chaque nom de couleur:

DELETE 
FROM colors 
WHERE c_id NOT IN (
     SELECT * 
     FROM (
         SELECT MIN(c_id) 
         FROM colors 
         GROUP BY c_name
     ) AS firstIds
    )

Remarque: ceci est similaire à la réponse de GMB, mais n'utilise pas de sous-requête corrélée.

Ensuite, les doublons peuvent être nettoyés avec quelque chose comme ça ...

DELETE 
FROM colors 
WHERE c_id NOT IN (
     SELECT MIN(c_id) 
     FROM colors 
     GROUP BY c_name
   )

cela conservera également les couleurs inutilisées, cependant ....

MySQL n'aime généralement pas les requêtes qui sélectionnent et suppriment simultanément dans la même table, il peut donc être nécessaire de l'exprimer ainsi pour "tromper" MySQL:

UPDATE fruit AS f 
INNER JOIN color As c ON f.cid = c.c_id
INNER JOIN (SELECT c_name, MIN(c_id) AS firstCid FROM color GROUP BY c_name) AS firsts
ON c.c_name = firsts.c_name
SET f.c_id = firsts.firstCid
;

0 commentaires