On me demande souvent jusqu’où peut-on aller en matière de récursivité avec SQL… Voici un exemple très simple pour comprendre comment effectuer une mise à jour récursive en une seule requête…
A partir de la question d’un internaute :
Je souhaiterai supprimer toutes les espaces consécutifs dans une colonne lorsque le nombre d’espace est supérieur à 1 et en laissant un seul espace au final… J’ai essayai avec la requête suivante mais elle ne fonctionne pas correctement :
UPDATE MaTable SET MaColonne = REPLACE (MaColonne,' ',' ') WHERE MaColonne LIKE '% %';
J’ai répondu en donnant la solution suivante…
1 – la table de notre essai :
CREATE TABLE T_ESPACE_ESP
(ESP_ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
ESP_DATA VARCHAR(32));
2 Рles donn̩es de notre essai :
INSERT INTO T_ESPACE_ESP VALUES
('ABCDEF'), -- 0 espace
(' ABCDEF'), ('ABC DEF'), ('ABCDEF '), (' ABC DEF '), -- 1 espace
(' ABCDEF'), ('ABC DEF'), ('ABCDEF '), (' ABC DEF '), -- 2 espaces
(' ABCDEF'), ('ABC DEF'), ('ABCDEF '),(' ABC DEF '), -- 3 espaces
(' ABC DEF '), ('ABC DEF'),('ABC DEF') ; -- divers
Soit :
ESP_ID ESP_DATA
----------- --------------------------------
1 ABCDEF
2 ABCDEF
3 ABC DEF
4 ABCDEF
5 ABC DEF
6 ABCDEF
7 ABC DEF
8 ABCDEF
9 ABC DEF
10 ABCDEF
11 ABC DEF
12 ABCDEF
13 ABC DEF
14 ABC DEF
15 ABC DEF
16 ABC DEF
Le problème est de passer de ces valeurs à celles figurant dans cette comparaison :
ESP_ID ESP_DATA ESP_DATA_NEW
----------- --------------------- ---------------------
6 ABCDEF ABCDEF
7 ABC DEF ABC DEF
8 ABCDEF ABCDEF
9 ABC DEF ABC DEF
10 ABCDEF ABCDEF
11 ABC DEF ABC DEF
12 ABCDEF ABCDEF
13 ABC DEF ABC DEF
14 ABC DEF ABC DEF
15 ABC DEF ABC DEF
16 ABC DEF ABC DEF
Notez que les données des 5 premières occurrences n’ont pas besoin d’être changées et n’apparaissent donc pas dans ce résultat !
Voici la requête magique qui fait cette mise à jour :
WITH
T0 AS
(SELECT ESP_ID, 1 AS N, REPLACE(ESP_DATA, ' ', ' ') AS DATA_NEW
FROM T_ESPACE_ESP
WHERE ESP_DATA LIKE '% %'
UNION ALL
SELECT T0.ESP_ID, N + 1, REPLACE(T0.DATA_NEW, ' ', ' ') AS DATA_NEW
FROM T0
WHERE DATA_NEW LIKE '% %'),
T1 AS
(SELECT ESP_ID, DATA_NEW
FROM T0 AS TT
WHERE N >= ALL (SELECT N
FROM T0 AS T
WHERE T.ESP_ID = TT.ESP_ID))
UPDATE C
SET ESP_DATA = DATA_NEW
FROM T_ESPACE_ESP AS C
INNER JOIN T1
ON C.ESP_ID = T1.ESP_ID;
Et le résultat :
ESP_ID ESP_DATA
----------- --------------------------------
1 ABCDEF
2 ABCDEF
3 ABC DEF
4 ABCDEF
5 ABC DEF
6 ABCDEF
7 ABC DEF
8 ABCDEF
9 ABC DEF
10 ABCDEF
11 ABC DEF
12 ABCDEF
13 ABC DEF
14 ABC DEF
15 ABC DEF
16 ABC DEF
Comment fonctionne t-elle ?
Dans la partie ancre de la requête récursive (CTE T0) on recherche les occurrences ayant au moins deux espaces, on réduit ces deux espaces à 1 et on attribut à ces lignes un poids de 1. Dans la partie itération de la requête récursive on poursuit le processus si le résultat de la passe précédente contient toujours deux espaces. En sortie de cette requête nous pouvons avoir plusieurs lignes simplifiées par rapport à une même valeur de la clef. Par exemple pour les lignes de clef 15 et 16, nous avons les données suivantes :
ESP_ID ESP_DATA N DATA_NEW
----------- -------------------- ----------- --------------
15 ABC DEF 1 ABC DEF
15 ABC DEF 2 ABC DEF
16 ABC DEF 1 ABC DEF
16 ABC DEF 2 ABC DEF
16 ABC DEF 3 ABC DEF
16 ABC DEF 4 ABC DEF
Car chaque itération récursive a réduit les doublons d’espaces en un seul, mais laisse possible de nouveaux doublons d’espaces par la concaténation des espaces unitaires réduits.
Il convient donc de prendre la dernière ligne comme valeur pour la mise à jour finale, et c’est tout le sens de la CTE T1 :
T1 AS
(SELECT ESP_ID, DATA_NEW
FROM T0 AS TT
WHERE N >= ALL (SELECT N
FROM T0 AS T
WHERE T.ESP_ID = TT.ESP_ID))
Au final il ne reste plus qu’à faire la mise à jour.
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
