Discussion :

[xounet]

Bonjour,

Je tente de mettre à jour une table contenant des colonne valeur, date, categorie. valeur peut être NULL. La mise à jour consiste à compléter la colonne valeur par date et par catégorie de sorte que la valeur soit égale à la valeur maximale (par date et par catégorie).

Code :

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

1
2
UPDATE table TT INNER JOIN (SELECT MAX(valeur) as Q, categorie as N, date_locale as DT FROM  table  GROUP BY categorie,date_locale ) T1 
								SET TT.valeur=Q WHERE TT.valeur IS NULL AND TT.categorie=N AND TT.date_locale=DT

Ce code utilisé sous php me retourne l'erreur :

Code :

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

Can't reopen table: 'TT'

Mais fonctionne lorsque testé sous PHPMYADMIN.
[EDIT]cela marche sous phpmyadmin car en réalité j'utilise une table temporaire alors que le test phpmyadmin se fait sur une table existante !!! [EDIT]
(je pense que c'est l'explication)

Dans tous les cas comment faire ce type de requête afin d'éviter cette erreur ?
Je précise utiliser MYSQL 5.6.

[xounet]

Deux solutions

Une solution mais nécessitant Mysql 8 :

https://dev.mysql.com/doc/refman/8.0...e%20functions.

Ou bien une autre mais nécessitant d'utiliser php et des multi requêtes en bouclant :

Code php :

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

1
2
3
4
5
6
7
8
9
10
11
12
// boucle ($critere) {
 
// boucle ($date) {
...
$sql=$sql."SELECT @max:=max(valeur),@categorie:=categorie FROM table WHERE date_locale='".$date ."' AND critere='".$critere."';	
												UPDATE table 
												SET valeur=@max 
												WHERE date_locale='".$date ."' 
														AND valeur IS NULL AND categorie=@categorie ;";
 
//fin boucle }
//fin boucle}

c'est grosso modo le principe, beaucoup moins rapide mais ça marche...

[Artemus24]

Salut xounet.

je pense que c'est l'explication

Oui, c'est l'explication.

Une table temporaire ne se comporte pas comme une table normal.
Tu peux remplir la table temporaire, et ensuite la lire, mais pas l'utiliser plusieurs fois dans des jointures (reopen table).
Dans ce cas, pourquoi ne pas créer une table normale, puis de la détruire quand tu n'en as plus besoin.

Je précise utiliser MYSQL 5.6.

Migrer vers mysql 8.0 est en effet une solution.

La boucle que vous proposez n'est pas franchement correcte.

Une autre solution consiste à faire votre mise à jour au moment de la création de votre table temporaire.
Il suffit d'ajouter une jointure supplémentaire (celle de votre update) à votre requête de remplissage.
La requête sera plus lourde, mais au moins, vous aurez résolu votre problème de reopen.

Pour résoudre votre problème, il nous faudrait les tables avec un jeu d'essai et le résultat attendu.

@+

[manzeki]

Deux solutions

Ou bien une autre mais nécessitant d'utiliser php et des multi requêtes en bouclant :
c'est grosso modo le principe, beaucoup moins rapide mais ça marche...

MySQL 5.* ne supporte pas d'insertion ou update multiples dans une telle boucle, à moins que vous inseriez une pause (sleep) php dans celle-ci.
Vous risquerez de faire planter ou faire crasher la base de données.
C'est par loin d'une attaque par deni de service de PHP sur MySQL

[manzeki]

Bonjour,
Dans tous les cas comment faire ce type de requête afin d'éviter cette erreur ?
Je précise utiliser MYSQL 5.6.

Essayez de passer par une procédure stockée comme ceci :

Code sql :

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
 
DELIMITER $$
CREATE PROCEDURE maximize()
BEGIN
 
	CREATE TEMPORARY TABLE IF NOT EXISTS volumes ( volume int, categorie int, date_locale date )
	AS SELECT MAX(volume), categorie, date_locale
	FROM movements
	GROUP BY categorie, date_locale;
 
	UPDATE movements v
	INNER JOIN volumes a ON a.categorie = v.categorie AND a.date_locale = v.date_locale
	SET v.volume = a.volume
	WHERE v.volume IS NULL;
 
END $$
DELIMITER ;

[xounet]

MySQL 5.* ne supporte pas d'insertion ou update multiples dans une telle boucle, à moins que vous inseriez une pause (sleep) php dans celle-ci.
Vous risquerez de faire planter ou faire crasher la base de données.
C'est par loin d'une attaque par deni de service de PHP sur MySQL

"Attaque" ?!? Mysql est très efficace et optimisé donc aucun souci quand on sait ce que l'on fait. Je confirme que mysql supporte les multi requêtes php UPDATE en série et autres et que c'est très rapide. PHP >7 est aussi optimisé pour gérer certaines redondances de fonctions

Essayez de passer par une procédure stockée comme ceci :

Peut être une solution mais je ne l'envisage pas de cette façon car je n'utilise pas de procédures stockées et la table temporaire doit pouvoir servir plus d'une fois.

Mysql 8 apporte il me semble la solution performante à ce type de problème puisqu'une seule ligne à ajouter suffirait :

Code :

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

WITH Tab_1 AS (SELECT * FROM table WHERE .... )

La migration vaut le coup rien que pour ça je pense mais faut trouver un peu de temps...

Je vais pour l'instant me contenter de PHP en optimisant le principe donné précédemment.

Merci pour vos remarques et les conseils.

[xounet]

J'avais pensé à un autre concept qui permettrai peut être de faire quelque chose :

Code :

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

UPDATE table SET Champ=valeur WHERE critere_1=A XOR critere_2=B

le résultat souhaité serait que la mise à jour s'effectue pour toutes les lignes avec le même couple de critères (A,B) sans pour autant le désigner explicitement laissant mysql choisir par exemple (NULL,1) ou (1,NULL) (alors que la requête ci dessus va bien mettre à jour toutes les lignes dont les critères sont (1,NULL) et (NULL,1) si l'on définit que A=1 ou NULL et B=1 ou NULL )
Est ce possible ?

[escartefigue]

Bonjour

Attention, NULL étant une valeur inconnue, la logique trivaluée prenant en compte le marqueur "null" produit

• NULL XOR valeur ==> faux
• NULL XOR NULL ==> faux

La preuve par l'exemple (avec MySQL V8.0)

Code SQL :

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
with tab1(C1, C2) as
    (select 1,    1      union all
     select 1,    null   union all
     select null, 1      union all
     select null, null 
    )
select C1
     , C2
     , case when C1=1 OR C2=1 then 'V'
            else 'F'
       end as 'OR'
     , case when C1=1 XOR C2=1 then 'V'
            else 'F'
       end as 'XOR'
     , case when C1=1 AND C2=1 then 'V'
            else 'F'
       end as 'AND'
from tab1

Résultat :
Pièce jointe 598887

[xounet]

oui effectivement je reprécise avec cette dernière remarque si l'on considère que critere_1 ou critere_2 doit avoir une valeur égale à NULL alors il faut écrire de cette façon :

Code :

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

UPDATE table SET Champ=valeur WHERE critere_1 IS NULL XOR critere_2 IS NULL

[escartefigue]

Oui, à la précision sémantique près qu'une table ne possède pas de champ. Un champ c'est une zone de formulaire de saisie ou une zone d'édition sur un état.
Dans une table, il y a des colonnes .
Et aussi qu'une valeur ne peut pas être égale à NULL, car NULL est un marqueur d'absence de valeur. La colonne est marquée "null" pour la ligne

C'est la raison pour laquelle la syntaxe n'est pas
CASE WHEN ma_colonne = NULL...
Mais
CASE WHEN ma_colonne IS NULL...

[escartefigue]

Pour les performances, vous avez grand intérêt à remplacer

Code :

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

WHERE CONCAT(lien_s,'_',repere)='21_1'

par

Code :

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

1
2
WHERE lien_s='21'
  AND repere='1'

En effet, l'utilisation d'une fonction telle que CONCAT rend les éventuels index inéligibles (prédicat dit "non sargable")

[xounet]

C'est plus rigoureux, j'ai pris cette habitude pour simplifier les programmes PHP parfois mais ici ça n'est pas vraiment justifié, je l'ai corrigé comme indiqué.

[xounet]

Après réflexion une réinstallation du serveur s'est imposée, j'ai donc migré vers Mysql 8 + PHP 8. Et finalement l'utilisation de "WITH"est moins utile que ce que j'avais cru comprendre puisque j'ai toujours le même problème lorsque dans une requête je manipule plus de 2 fois une table temporaire :

Code :

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

1
2
3
 
 
WITH T2 AS (SELECT d_m,fl,s_t,date_locale FROM table_temp WHERE d_m IS NOT NULL group BY date_locale,fl,s_t,d_m) UPDATE table_temp JOIN T2 SET d_m=T2.d_m WHERE n_t=T2.s_t AND date_locale=T2.date_locale

Can't reopen table: 'table_temp'

[xounet]

Une solution simple consiste à créer une autre table temporaire en copie pour pouvoir faire une mise à jour de la table et l'utiliser 2x dans la requête c'est compatible partout, mais j'imaginais pouvoir faire quelque chose de plus direct :

Code :

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

UPDATE table_temp TT INNER JOIN (SELECT MAX(d_m) as Q, n_t as N, date_locale as DT FROM table_temp_COPIE GROUP BY n_t,date_locale ) T1 SET TT.d_m=Q WHERE TT.d_m IS NULL AND TT.n_t=N AND TT.date_locale=DT

[escartefigue]

Vous pouvez tout à fait utiliser deux fois la même CTE dans une requête.

Par ailleurs après mise en forme de votre requête (sur une seule ligne c'est illisible), on se rend compte que vous n'avez mis aucun critère de jointure (opérateur JOINsans ON) !
Du coup vous faites un produit cartésien puis une restriction sur le résultat, à ne surtout pas faire sur des tables volumineuses.

De plus, les noms de colonnes particulièrement sibyllins n'aident pas à la compréhension d_m, fl, s_t... c'est quoi tout ça ?
Impossible de savoir si parmi ces colonnes, il y a le nécessaire pour pouvoir réaliser la jointure requise...

[xounet]

En améliorant la requête de cette façon :

Code :

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

WITH T2 AS (SELECT d_m,fl,s_t,date_locale FROM table_temp WHERE d_m IS NOT NULL group BY date_locale,fl,s_t,d_m) UPDATE table_temp INNER JOIN T2 ON (n_t=T2.s_t AND date_locale=T2.date_locale AND T2.d_m IS NOT NULL)  SET d_m=T2.d_m

Cela ne change rien la requête n'est pas acceptée toujours la même erreur ne peut réouvrir la table

[escartefigue]

Que donne ce cas de test chez vous ?
Pour moi c'est ok

Code SQL :

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
create table T1(T1_ident integer primary key,
                T1_data  char(4) not null
               )
; 
insert into T1(T1_ident, T1_data) 
values (001, 'AAAA')
     , (002, 'AZER')
     , (003, 'POIU')
     , (004, 'XXXX')
;
select * from T1
;
with C1 (C1_ident, C1_data) as 
    (select T1_ident, T1_data
     from T1
     where T1_ident < 3
    )
select * from T1
where exists
     (select 1 
      from C1
      where C1_ident=T1_ident)
union all
select * from T1
where not exists
     (select 1 
      from C1
      where C1_ident=T1_ident)
 
;

Ma remarque concernant la présentation de la requête subsiste : une requête écrite sur une seule ligne c'est illisible

[EDIT] j'ai oublié le test en mise à jour, que donne cette requête-ci (toujours ok pour moi) :

Code SQL :

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
create table T1(T1_ident integer primary key,
                T1_data  char(4) not null
               )
; 
insert into T1(T1_ident, T1_data) 
values (001, 'AAAA')
     , (002, 'AZER')
     , (003, 'POIU')
     , (004, 'XXXX')
;
select * from T1
;
with C1 (C1_ident, C1_data) as 
    (select T1_ident, T1_data
     from T1
     where T1_ident < 3
    )
update T1  as T1
inner join C1  as C1
   on C1.C1_ident=T1.T1_ident
set T1.T1_data='YYYY'
;
select * from T1
;

[xounet]

Bonjour,

Merci pour cette réponse et l'effort fourni pour produire un test réutilisable.
En faisant la requête test j'ai bien le même problème :

Impossible de réouvrir la table: 'T1

Puisque en effet je parle de tables temporaires et pas de tables permanentes c'est là la différence et c'est pour cela que cela ne marche pas : voici la requête correspondant à ce que je cherche à faire :

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
create temporary table T1(T1_ident integer primary key,
                T1_data  char(4) not null
               )
; 
insert into T1(T1_ident, T1_data) 
values (001, 'AAAA')
     , (002, 'AZER')
     , (003, 'POIU')
     , (004, 'XXXX')
;
select * from T1
;
with C1 (C1_ident, C1_data) as 
    (select T1_ident, T1_data
     from T1
     where T1_ident < 3
    )
update T1  as T1
inner join C1  as C1
   on C1.C1_ident=T1.T1_ident
set T1.T1_data='YYYY'
;
select * from T1
;

La solution que j'ai trouvée et décrite plus haut serait au pire :

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
create temporary table T1(T1_ident integer primary key,
                T1_data  char(4) not null
               )
; 
 
insert into T1(T1_ident, T1_data) 
values (001, 'AAAA')
     , (002, 'AZER')
     , (003, 'POIU')
     , (004, 'XXXX')
;
create temporary table T2 SELECT * FROM T1
; 
select * from T1
;
select * from T2
;
update T1
inner join (SELECT * FROM T2 WHERE T1_ident<3)  as T2
   on T2.T1_ident=T1.T1_ident
set T1.T1_data='YYYY'
;
select * from T1
;

[Artemus24]

Salut xounet.

Pourquoi persistez-vous à utiliser des tables temporaires ?
Créez un table mysql normal et quand vous n'en avez plus besoin, détruisez là !

@+

[xounet]

Bonjour Artemus24,

J'utilise la table temporaire pour faire de nombreux calculs au final je souhaite obtenir la table dans sa version définitive et l'enregistrer sur le disque. Cela évite donc de nombreux enregistrements de quelques octets sur le disque dur et évite de très nombreuses lectures tout se fait en mémoire vive et l'avantage c'est aussi de bénéficier de la très grande rapidité d'exécution des requêtes SQL et d'une plus grande facilité de programmation.