UPDATE sur plusieurs colonnes à la fois

**yuukuari** · 17/11/2009, 19h42

Bonjour

Je travaille sur une base de données assez conséquente et lorsque je fais un update classique, ça plante:

Code :

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

UPDATE table SET champ1 = (requête1), champ2 = (requête2), etc..

Donc j'ai décomposé la requête en faisant un update sur chaque colonne une par une:

Code :

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

1
2
3
UPDATE table SET champ1 = (requête1);
UPDATE table SET champ2 = (requête2);
etc...

Ca ne plante plus mais c'est un peu lourd à écrire tout ça :/ Surtout pour certains champs qui utilisent la même requête pour être remplis (comme si requête 1 = requête 2).

Ma question est donc la suivante, peut-on faire un update de plusieurs champs d'une table de cette façon:

Code :

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

UPDATE table SET (champ1, champ2, champ3) = (requête);

Par exemple (fictif :p) :

Code :

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

1
2
3
4
UPDATE `fruits` SET (`fruits`.`couleur`, `fruits`.`prix`) = (
     SELECT `aliments`.`couleur`, `aliments`.`prix` FROM `aliments`
     WHERE `aliments`.`type` = 'fruit'
);

Je ne sais pas si la commande existe, j'ai pas mal cherché et j'ai vu une seule personne en parler mais chez moi ça marche pas :/ Ce serait pratique quand même ^^

**CinePhil** · 17/11/2009, 23h12

Envoyé par yuukuari

Je travaille sur une base de données assez conséquente et lorsque je fais un update classique, ça plante:

Code :

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

UPDATE table SET champ1 = (requête1), champ2 = (requête2), etc..

Et tu récoltes quoi ? Un message d'erreur ?

On peut voir la vraie requête avec une idée du volume et un exemple de données ?

**ced** · 18/11/2009, 10h51

Bonjour,

Inspire-toi de la solution présentée dans la discussion suivante : http://www.developpez.net/forums/d82...date-jointure/

ced

**yuukuari** · 18/11/2009, 11h28

Envoyé par CinePhil

Et tu récoltes quoi ? Un message d'erreur ?

On peut voir la vraie requête avec une idée du volume et un exemple de données ?

Le but c'est de calculer des statistiques à la semaine sur des bandes dessinées en ligne. Ça me permet de donner une note à chaque album et donc de les classer par "popularité à la semaine".

La requête est la suivante:

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
insert into `album_statistics` (album_id, created_on) 
select `albums`.`id`, CURDATE() from `albums` where `albums`.`displayed` = 1 and `albums`.`deleted` = 0;
 
update `album_statistics` set 
 
	`album_statistics`.`week_fan_count` = (
		select count(*) from `favorite_albums`
		where (DATEDIFF(CURDATE(), `favorite_albums`.`created_on`) < 7) and `favorite_albums`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
	),
 
	`album_statistics`.`week_read_count` = (
		select count(*) from `album_views`
		where (DATEDIFF(CURDATE(), `album_views`.`date`) < 7) and `album_views`.`album_id` = `album_statistics`.`album_id` 
		and `album_views`.`read` = 1 and `album_statistics`.`created_on` = CURDATE()
	),
 
	`album_statistics`.`week_critic_count` = (
		select count(*) from `critics`
		where (DATEDIFF(CURDATE(), `critics`.`created_on`) < 7) and `critics`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
	),
 
	`album_statistics`.`week_global_avg` = (
		select avg(`critics`.`global_rating`) from `critics`
		where (DATEDIFF(CURDATE(), `critics`.`created_on`) < 7) and `critics`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
	),
 
	`album_statistics`.`week_scenario_avg` = (
		select avg(`critics`.`scenario_rating`) from `critics`
		where (DATEDIFF(CURDATE(), `critics`.`created_on`) < 7) and `critics`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
	),
 
	`album_statistics`.`week_illustration_avg` = (
		select avg(`critics`.`illustration_rating`) from `critics`
		where (DATEDIFF(CURDATE(), `critics`.`created_on`) < 7) and `critics`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
	)
;
 
update `albums`, `album_statistics` 
	set `albums`.`week_popularity` = (
		calcul de popularité des albums
	) 
	where `albums`.`id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
;

Pour le volume de données, pour l'instant j'ai environ 360 albums, 1000 critiques et 120 000 lectures donc ça fait pas mal de données et quand je lance la requête complète la page mysql se bloque :/

C'est pour ça que je l'ai séparée en plusieurs requêtes:

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
update `album_statistics` 
set `album_statistics`.`week_fan_count` = (
	select count(*) from `favorite_albums`
	where (DATEDIFF(CURDATE(), `favorite_albums`.`created_on`) < 7) and `favorite_albums`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
);
 
update `album_statistics` 
set `album_statistics`.`week_critic_count` = (
	select count(*) from `critics`
	where (DATEDIFF(CURDATE(), `critics`.`created_on`) < 7) and `critics`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
);
 
update `album_statistics` 
set `album_statistics`.`week_read_count` = (
	select count(*) from `album_views`
	where (DATEDIFF(CURDATE(), `album_views`.`date`) < 7) and `album_views`.`album_id` = `album_statistics`.`album_id` 
	and `album_views`.`read` = 1 and `album_statistics`.`created_on` = CURDATE()
);
 
update `album_statistics` 
set `album_statistics`.`week_global_avg` = (
	select avg(`critics`.`global_rating`) from `critics`
	where (DATEDIFF(CURDATE(), `critics`.`created_on`) < 7) and `critics`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
);
 
update `album_statistics` 
set `album_statistics`.`week_scenario_avg` = (
	select avg(`critics`.`scenario_rating`) from `critics`
	where (DATEDIFF(CURDATE(), `critics`.`created_on`) < 7) and `critics`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
);
 
update `album_statistics` 
set `album_statistics`.`week_illustration_avg` = (
	select avg(`critics`.`illustration_rating`) from `critics`
	where (DATEDIFF(CURDATE(), `critics`.`created_on`) < 7) and `critics`.`album_id` = `album_statistics`.`album_id` and `album_statistics`.`created_on` = CURDATE()
);

Pour les 4 champs utilisant les critiques (week_critic_count, week_global_avg, week_scenario_avg et week_illustration_avg) je pensais faire une seule requête pour simplifier les choses.

Envoyé par ced

Bonjour,

Inspire-toi de la solution présentée dans la discussion suivante : http://www.developpez.net/forums/d82...date-jointure/

ced

J'avais pas pensé à faire comme ça je vais essayer de voir si ça marche

**yuukuari** · 18/11/2009, 11h46

J'ai essayé de refaire un peu à la façon du lien que tu m'as envoyé (qui revient en fait un peu à mon update à la fin):

Code :

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

1
2
3
4
5
6
UPDATE `albums`, `album_statistics` 
	SET `albums`.`week_popularity` = (
		calcul de popularité des albums
	) 
	WHERE `albums`.`id` = `album_statistics`.`album_id` AND `album_statistics`.`created_on` = CURDATE()
;

J'ai donc fait ça:

Code :

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

1
2
3
4
5
6
update `album_statistics`, `critics` 
set `album_statistics`.`week_critic_count` = count(*),
	`album_statistics`.`week_global_avg` = avg(`critics`.`global_rating`),
	`album_statistics`.`week_scenario_avg` = avg(`critics`.`scenario_rating`),
	`album_statistics`.`week_illustration_avg` = avg(`critics`.`illustration_rating`)
where `album_statistics`.`album_id` = `critics`.`album_id` and (DATEDIFF(CURDATE(), `critics`.`created_on`) < 7) and `album_statistics`.`created_on` = CURDATE();

Il me dit "#1111 - Utilisation invalide de la clause GROUP "
J'ai l'impression que le count(*) c'est pas vraiment comme ça qu'on fait

..

**CinePhil** · 18/11/2009, 11h48

Moi je ferais une table temporaire avec tous les comptages et ensuite je mettrais à jour avec une jointure sur la table temporaire.
Ca ne ferait que deux requêtes et la table temporaire peut être indexée.

**yuukuari** · 18/11/2009, 12h05

Je connaissais pas les tables temporaires (je débute) mais ça a l'air vraiment bien :O

Et surtout ça colle apparemment avec ce que j'ai envie de faire car à chaque fois que je passe mon script, je crée d'abord une ligne pour chaque album de la table `albums` dans ma table `album_statistics` puis je fais des traitements dessus et à la fin je calcule la popularité de l'album, que je sauvegarde dans un champ de la table `albums`.

Là je me fais chi** à recréer des lignes dans la table chaque jour et toutes mes requêtes se font en fonction de la date du jour..

En plus, comme ça je peux faire la même chose pour la popularité d'un album au mois, à l'année, etc...

**yuukuari** · 18/11/2009, 12h32

Bon en attendant d'utiliser une table temporaire, j'ai réussi à résoudre mon autre problème héhé

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
update `album_statistics` as a 
left outer join (
	select c.`album_id`, count(*) as total, avg(c.`global_rating`) as global_rating, avg(c.`scenario_rating`) as scenario_rating, avg(c.`illustration_rating`) as illustration_rating
	from `critics` as c 
	inner join 
	where DATEDIFF(CURDATE(), c.`created_on`) < 7
	group by c.`album_id`
) as ca on a.`album_id` = ca.`album_id` 
set a.`week_critic_count` = ifnull(ca.total, 0),
	a.`week_global_avg` = ifnull(ca.global_rating, 0),
	a.`week_scenario_avg` = ifnull(ca.scenario_rating, 0),
	a.`week_illustration_avg` = ifnull(ca.illustration_rating, 0);

En fait le select il fallait le faire DANS le join de tables

J'ai rajouté le "left outer join" et les ifnull pour les cas où un album n'a pas de critique.

**yuukuari** · 18/11/2009, 14h46

Et voilà en faisant une table temporaire tout marche niquel

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
CREATE TEMPORARY TABLE `album_statistics` (
  `id` int(11) NOT NULL auto_increment,
  `album_id` int(11) NOT NULL,
  `week_fan_count` int(11) NOT NULL DEFAULT '0',
  `week_critic_count` int(11) NOT NULL DEFAULT '0',
  `week_read_count` int(11) NOT NULL DEFAULT '0',
  `week_global_avg` decimal(10,5) DEFAULT null,
  `week_illustration_avg` decimal(10,5) DEFAULT null,
  `week_scenario_avg` decimal(10,5) DEFAULT null,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
insert into `album_statistics` (album_id) 
select `albums`.`id` from `albums` where `albums`.`displayed` = 1 and `albums`.`deleted` = 0;
 
update `album_statistics` 
set `album_statistics`.`week_fan_count` = (
	select count(*) from `favorite_albums`
	where (DATEDIFF(CURDATE(), `favorite_albums`.`created_on`) < 7) and `favorite_albums`.`album_id` = `album_statistics`.`album_id`
);
 
update `album_statistics` 
set `album_statistics`.`week_read_count` = (
	select count(*) from `album_views`
	where (DATEDIFF(CURDATE(), `album_views`.`date`) < 7) and `album_views`.`album_id` = `album_statistics`.`album_id` 
	and `album_views`.`read` = 1
);
 
update `album_statistics` as a 
left outer join (
	select c.`album_id`, count(*) as total, avg(c.`global_rating`) as global_rating, avg(c.`scenario_rating`) as scenario_rating, avg(c.`illustration_rating`) as illustration_rating
	from `critics` as c 
	inner join `albums` on `albums`.`id` = c.`album_id` 
	where DATEDIFF(CURDATE(), c.`created_on`) < 7 and `albums`.`deleted` = 0 and `albums`.`displayed` = 1 
	group by c.`album_id`
) as ca on a.`album_id` = ca.`album_id` 
set a.`week_critic_count` = ifnull(ca.total, 0),
	a.`week_global_avg` = ifnull(ca.global_rating, 0),
	a.`week_scenario_avg` = ifnull(ca.scenario_rating, 0),
	a.`week_illustration_avg` = ifnull(ca.illustration_rating, 0);
 
update `albums` 
inner join `album_statistics` on `albums`.`id` = `album_statistics`.`album_id` 
	set `albums`.`week_popularity` = (
		calcul de la popularité
	) 
	where `albums`.`id` = `album_statistics`.`album_id`
;

Si j'ai le courage je ferai un script automatique ^^
Merci de votre aide à tous les 2!

yuukuari

UPDATE sur plusieurs colonnes à la fois

Requêtes MySQL

Discussions similaires

Partager

Partager