[RESOLU] requète UPDATE sur colone unique avec suffix automatique

Mammouth du PHP | 1967 Messages

14 mai 2024, 10:28

bonjour,

Je simplifie mon problème à ceci, j'ai une table avec une série de titre, certain sont en double et doivent le rester mais je voudrais un champ unique qui correspond à ce titre en minuscule avec un suffix si l'unicitié n'est pas respecté.

donc imaginons une table avec ces noms je voudrais remplir la colonne unique avec le :om après =>
jean => jean
john => john
Paul => paul
jean => jean-1
Stef => stef
Jean => jean-2
John => john-1

Je le fait par des requêtes successives

Code : Tout sélectionner

UPDATE IGNORE table SET unique = lower(nom); UPDATE IGNORE table SET unique = CONCAT(lower(nom), '-1'); UPDATE IGNORE table SET unique = CONCAT(lower(nom), '-2');
Et ca marche mais j'aimerai m'affranchir de répéter la requête car je ne sais pas nécessairement combien de fois je vais devoir la lancer.

Quelqu'un aurait une solution SQL ?
Spols
pour les fan de rubik's cube ou pour les curieux ==> le portail francophone du rubik's cube

Avatar du membre
Administrateur PHPfrance
Administrateur PHPfrance | 9663 Messages

15 mai 2024, 12:26

En ajoutant du PHP, mais je ferai une petite boucle qui fait un SELECT pour compter l nombre de doublons et si il en reste répéter l'UPDATE autant de fois que nécessaire en incrémentant de 1 à chaque passage...
Quand tout le reste a échoué, lisez le mode d'emploi...

Avatar du membre
Mammouth du PHP | 1489 Messages

15 mai 2024, 16:35

Tu peux utiliser une approche basée sur une sous-requête qui compte le nombre d'occurrences de chaque titre, puis utiliser cette information pour générer le champ unique avec le suffixe approprié.
UPDATE `table` t
JOIN (
    SELECT 
        nom,
        CASE
            WHEN COUNT(*) > 1 THEN CONCAT(lower(nom), '-', ROW_NUMBER() OVER (PARTITION BY lower(nom) ORDER BY nom))
            ELSE lower(nom)
        END AS unique_nom
    FROM 
        `table`
    GROUP BY 
        lower(nom)
) u ON t.nom = u.nom
SET t.unique = u.unique_nom;
Explication :
  1. J'utilise une sous-requête pour regrouper les noms en minuscules et compter leur occurrence.
  2. Dans cette sous-requête, j'utilise la fonction ROW_NUMBER() pour attribuer un numéro de ligne à chaque occurrence d'un titre en double, partitionné par le nom en minuscules et trié par le nom.
  3. Ensuite, j'utilise une jointure pour mettre à jour la table originale avec les valeurs générées dans la sous-requête.
  4. Dans la clause SET, j'attribue la valeur du champ unique en utilisant le résultat de la sous-requête, qui génère le champ unique avec le suffixe approprié si nécessaire.
A tester.

Mammouth du PHP | 1967 Messages

16 mai 2024, 11:13

Merci de votre aide.

Pour la solution php j'ay avais bien sur pensé mais comme il s'agit d'une migration de bdd pouvant encore être modifié régulièrement je préférais une solution pure sql pour n'avoir qu'a importer un fichier .sql et mettre la nouvelle bdd à jour.

Pour la 2ème solution, mon serveur mysql n'accepte pas le ROW_NUMBER malheureusement, j'ai tenté quelque chose avec une variable incrémentée mais rien de concluant.

Je vais garder une méthode empirique avec 7 version de la même requête (empiriquement 7 suffit)
Spols
pour les fan de rubik's cube ou pour les curieux ==> le portail francophone du rubik's cube

Avatar du membre
Mammouth du PHP | 1489 Messages

16 mai 2024, 14:16

Je m'en doutais.

Mets ta solution au cas où d'autres sont dans la même impasse. ;)

Mammouth du PHP | 1967 Messages

17 mai 2024, 10:12

Je suis donc resté sur mon code présenté en premier post mais avec une subtilité.

Code : Tout sélectionner

UPDATE IGNORE table SET unique = lower(nom) WHERE unique IS NULL; UPDATE IGNORE table SET unique = CONCAT(lower(nom), '-1') WHERE unique IS NULL; UPDATE IGNORE table SET unique = CONCAT(lower(nom), '-2') WHERE unique IS NULL; ...
le IGNORE permet de ne pas soulever d'erreur et d'ignorer simplement l'insertion qui ne serait pas unique et la laisse pour les instructions suivantes. Le nombre d'instruction est à déterminer pour votre cas.
le WHERE sert à ne pas tenté de réécrire les lignes déjà complétée.
Bien sur si il y a besoin de regénérer le contenu de la colonne il faut d'abords la vider.
Spols
pour les fan de rubik's cube ou pour les curieux ==> le portail francophone du rubik's cube