Discussion :

[xounet]

Bonjour,

Dans une requête SQL complexe, je dois réaliser les étapes suivantes :
1/extraire des données d'une table table1 pour les stocker dans une table "table_base"
2/extraire des résultats d'une requête sur la "table_base" et les stocker dans une table "table_calculs_1"
3/extraire des résultats d'une requête sur la "table_calculs_1" et les stocker dans une table "table_calculs_2"
4/Mettre à jour la table initiale table1 selon des critères qui sont regroupés dans la "table_calculs_2"

Ceci n'est visiblement pas possible dans une seule requête telle que celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH 	
	table_base as (SELECT t1.n1,t1.n2,t1.n3, ....
																	FROM table1 t1, table1  t2, table1  t3 
																	WHERE ... IS NULL AND ...<.. AND ...
																	ORDER BY ... DESC ,.. ASC..),
										table_calculs_1 as (
																	SELECT max(n1) as valeur_precedente,n2 as valeur_nulle,min(n3) as valeur_suivante 
																	FROM table_interpolation_base 
																	GROUP BY n2),
										table_calculs_2 as (
																	SELECT .....
																	FROM table_calculs_1 , table_base 
																	WHERE n1=valeur_precedente AND n2=valeur_nulle AND n3=valeur_suivante)
										UPDATE table1 Q INNER JOIN table_calculs_2  T SET Q.test2=T.val ON Q.num_ligne=T.n2)

J'ai le message d'erreur :

Can't reopen table: 'table1'

Cependant je ne trouve pas vraiment de solution pour contourner ce problème.

[escartefigue]

Bonjour,

merci de communiquer le SQL complet et exact de la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
table_base as 
         (SELECT t1.n1,t1.n2,t1.n3, ....
	  FROM table1 t1, table1  t2, table1  t3 
	  WHERE ... IS NULL AND ...<.. AND ...
          ORDER BY ... DESC ,.. ASC..
         )

Par ailleurs, dans l'ordre UPDATE, il ne faut pas mettre la clause SET entre les mots-clefs JOIN et ON, mais après la clause de jointure
Ce qui donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
UPDATE table1 T1 
INNER JOIN table_calculs_2  TC2 
   ON TC2.num_ligne=T1.n2
SET TC2.test2=T.val

Enfin, écrire les jointures sous la forme FROM T1, T2, ..., Tn en mettant les critères de jointure dans la clause WHERE, c'est vraiment pas top

[xounet]

Bonjour, merci pour ces bons conseils.

En partant de mon problème de base j'ai simplifié la requête pour plus d'efficacité du coup voici le problème :
A partir de cette table table1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
--
-- Structure de la table `table1`
--
 
CREATE TABLE `table1` (
  `num_ligne` int NOT NULL,
  `date` datetime DEFAULT NULL,
  `test` decimal(10,1) DEFAULT NULL COMMENT 'ceci est un commentaire',
  `test2` int DEFAULT NULL COMMENT 'ceci est un commentaire'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
 
--
-- Déchargement des données de la table `table1`
--
 
INSERT INTO `table1` (`num_ligne`, `date`, `test`, `test2`) VALUES
(1, '2022-01-01 00:00:00', '4.0', 0),
(2, '2022-01-05 00:00:00', '4.0', 0),
(3, '2022-01-08 00:00:00', '489.0', 0),
(4, '2022-01-10 10:54:03', '-456.0', NULL),
(5, '2022-01-15 00:00:00', '7445.0', 226),
(6, '2022-01-17 00:00:00', '45.0', 325),
(7, '2022-01-19 00:00:00', '-4.0', NULL),
(8, '2022-01-20 00:00:00', '-5000.0', 164),
(9, '2022-01-22 00:00:00', '-4.0', 5),
(10, '2022-01-23 00:00:00', '78.0', NULL),
(11, '2022-01-24 00:00:00', NULL, 117),
(12, '2022-01-25 00:00:00', '7.0', 289),
(13, '2022-01-26 00:00:00', NULL, 461);
 
--
-- Index pour les tables déchargées
--
 
--
-- Index pour la table `table1`
--
ALTER TABLE `table1`
  ADD PRIMARY KEY (`num_ligne`),
  ADD UNIQUE KEY `test num ligne` (`test`,`num_ligne`);
 
--
-- AUTO_INCREMENT pour les tables déchargées
--
 
--
-- AUTO_INCREMENT pour la table `table1`
--
ALTER TABLE `table1`
  MODIFY `num_ligne` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;
COMMIT;

je souhaite d'abord la cloner en une table temporaire pour ensuite pouvoir travailler non pas sur la table d'origine mais sur le clone qui est en mémoire vive, donc voici le type de requête que j'essaie d'utiliser :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
CREATE TEMPORARY TABLE tt1 SELECT * FROM test.table1;
	WITH 	
	table_calculs1 as (SELECT t2.num_ligne n2,t1.test as val FROM tt1 t1 WHERE t2.test2 IS NULL AND t1.num_ligne=t2.num_ligne)                                 
UPDATE tt1 INNER JOIN table_calculs1 T  ON num_ligne=T.n2 SET test2=T.val WHERE val IS NOT NULL

Et voici le message que j'ai en retour :

MySQL a répondu : Documentation
#1137 - Impossible de réouvrir la table: 'tt1

Le problème sur lequel j'ai déjà réfléchi pas mal de fois provient me semble t il de l'utilisation de tables temporaires qui ne permettent pas d'être utilisées à plusieurs reprises dans une requêtes autrement qu'en lecture. La solution est de créer d'autres tables temporaires copies, mais table1 pouvant être très "grand" cela me semble pas adapté de le copier deux ou trois fois en mémoire vive. C'est pour cela que je cherche d'autres solutions.

[escartefigue]

Bonjour,

Les commentaires du script sont erronés, on parle de "déchargement" et de tables "déchargées" alors qu'au contraire il s'agit de chargements par insert
Ca semble un détail, mais tant qu'à commenter, autant que ça corresponde au code

Ensuite, cette déclarative de CTE est erronée, l'alias T2 ne correspondant à aucune table

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
(SELECT t2.num_ligne n2
      , t1.test         as val 
 FROM tt1 t1 
 WHERE t2.test2 IS NULL 
   AND t1.num_ligne=t2.num_ligne)

[xounet]

Merci pour ton retour.
Concernant les commentaires, ceux-ci proviennent de phpmyadmin pour la partie exportation de tables donc j'ai pas la main dessus.

Concernant la requête, je suis désolé effectivement j'ai fait une erreur de copie en simplifiant ma requête en fait j'utilise bien deux fois la table tt1, l'exemple est vraiment basique et semble un peu inutile, mais l'idée derrière cet exemple c'est de compléter les valeurs nulles d'une table par des valeurs non nulles de cette même table en procédant de cette façon (table temporaire+cte).
Voici la requête corrigée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
CREATE TEMPORARY TABLE tt1 SELECT * FROM test.table1;
	WITH 	
	table_calculs1 as (SELECT t2.num_ligne n2,t1.test as val FROM tt1 t1,tt1 t2 WHERE t2.test2 IS NULL AND t1.num_ligne=t2.num_ligne)                                 
UPDATE tt1 INNER JOIN table_calculs1 T  ON num_ligne=T.n2 SET test2=T.val WHERE val IS NOT NULL

En espérant que cela répond aux interrogations concernant le problème.

[xounet]

Visiblement rien ne permet d'utiliser les tables temporaires de cette façon c'est expliqué en partie :
https://dev.mysql.com/doc/refman/5.7...-problems.html
et passer par des CTE pour manipuler des tables temporaires n'est pas forcément la solution même si ça peut fonctionner parfois. Dommage.
En fait les CTE permettent surtout d'améliorer l'écriture de requêtes complexes mais n'apporte pas réellement de fonctionnalités nouvelles.