Mise à jour d'une table à partir du résultat d'une requête

**zoharcryss** · 17/08/2009, 14h33

Bonjour,

je cherche à faire quelque chose qui me semble relativement simple mais apriori pas assez car je n'y arrive pas...

Je vous explique, je cherche à UPDATE une table à partir d'un résultat d'une requête qui touche d'autre table (calcul de moyenne etc...). Je ne sais pas du tout comment m'y prendre et ne vois pas dans quel sens prendre le problème.

Informations :

SGBD : MySQL

Structure de ma table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE IF NOT EXISTS `mjoba` (
  `Job_idJob` int(11) NOT NULL,
  `Quantite` int(2) default NULL,
  `NomJob` varchar(30) default NULL,
  `Date` year(4) default NULL,
  `Moyenne` time default NULL,
  KEY `MA_FKIndex1` (`Job_idJob`),
  KEY `Quantite` (`Quantite`),
  KEY `NomJob` (`NomJob`),
  KEY `Date` (`Date`),
  KEY `Moyenne` (`Moyenne`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Ma requête (qui fonctionne et qui ne pose pas de problème)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
			COUNT(Job_idJob),
			Job.idJob,
			Job.NomJob,
			SUBSTR(Debut,1,4),
			SEC_TO_TIME(Avg(TIME_TO_SEC(Temp)))
FROM DureeJob 
INNER JOIN Job ON DureeJob.Job_idJob=Job.idJob 
AND job.nomjob LIKE '%-%A-%'
GROUP BY 
			SUBSTR(DEBUT,1,4),
			job_idjob 
ORDER BY `Job`.`NomJob` ASC

Extrait du résultat :

Job_idJob, Quantite, NomJob, Date, Moyenne
6400, 1, PROCZ-100A-010D, 2009, 00:00:10
6401, 3, PROCZ-100A-020D, 2009, 00:00:19
6402, 21, PROCZ-100A-030D, 2009, 00:00:19
6403, 5, PROCZ-100A-040D, 2009, 00:00:11

Explications complémentaires :

Ce que j'aimerais pouvoir faire ici, c'est d'insérer dans la table MJOBA le résultat de ma requête sauf que les arguments de UPDATE comme le SET m'induisent un peu en erreur... J'ai aussi pensé à insérer dans une table temporaire le résultat de la requête pour ensuite les insérer mais je n'ai rien pu faire de concluant...

Aussi, j'ai un problème assez particulier, la requête sort un résultat bien groupé d'une certaine manière et séparer les arguments chamboulerais un peu tout (d'où ma difficulté à faire une "UPDATE TABLE SET CHAMP1=X, CHAMP2=Y"). La requête doit UPDATE la table et non pas l'INSERT.

Si quelqu'un a une idée de génie ca serait formidable !
Merci d'avance à tous !

**zoharcryss** · 17/08/2009, 15h13

Ah aussi petite précision, j'aimerais en gros transvaser le résultat de ma requête dans ma table MJOBA et ne pas mettre à jour les lignes déjà présentes si elle sont identiques à celles de la requête. J'espère que cela aidera à la compréhension de mon problème.

**CinePhil** · 17/08/2009, 15h45

Bonjour et merci d'avoir respecté les règles du forum, ça fait plaisir et c'est clair.

Il n'y a pas de clé primaire dans la table 'mjoba' mais me trompé-je en supposant que la colonne 'Job_idJob' est une clé candidate, c'est à dire qu'il n'y a pas deux 'Job_idJob' identiques dans la table ?

ne pas mettre à jour les lignes déjà présentes si elle sont identiques à celles de la requête.

Identiques sur toutes les colonnes ?

Tu peux faire un UPDATE avec une jointure. Ca donnerait quelque chose du genre :

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
UPDATE mjoba
INNER JOIN (
  SELECT 
    COUNT(Job_idJob) AS Qte,
    Job.idJob,
    Job.NomJob,
    SUBSTR(Debut,1,4) AS Annee,
    SEC_TO_TIME(Avg(TIME_TO_SEC(Temp))) AS Moyenne
  FROM DureeJob 
  INNER JOIN Job ON DureeJob.Job_idJob = Job.idJob 
  WHERE job.nomjob LIKE '%-%A-%'
  GROUP BY 
    SUBSTR(DEBUT,1,4),
    job_idjob 
  ORDER BY Job.NomJob ASC
) AS tmp ON mjoba.Job_idJob = tmp.idJob
SET 
  mjoba.Quantite = tmp.Qte,
  mjoba.NomJob = tmp.NomJob
  mjoba.Date = tmp.Annee
  mjoba.Moyenne = tmp.Moyenne
WHERE 
  mjoba.Job_idJob = tmp.idJob AND (
    mjoba.Quantite <> tmp.Qte OR
    mjoba.NomJob <> tmp.NomJob OR
    mjoba.Date <> tmp.Annee OR
    mjoba.Moyenne <> tmp.Moyenne
  )

A adapter selon ton besoin réel.

Au passage, c'est une mauvaise idée d'avoir appelé une colonne 'Date' car c'est un mot réservé du SQL.

**zoharcryss** · 17/08/2009, 16h18

Alors d'abord merci beaucoup pour ta réponse. Pour ce qui est des Job_idJob, ce n'est pas un UNIQUE.
Enfaite dans cette table je fais la moyenne des JOBS annuels par année (un peu bebete mais certains jobs annuel peuvent être lancés plusieurs fois par an...)
La seule chose pour laquelle je ne veux pas re-UPDATE des lignes identiques est la perte de temps CPU-elapsed.
Si la totalité de la ligne (moyenne comprise) est identique, rien ne sert de l'update...
Je vais tester le tout !

**Yanika_bzh** · 17/08/2009, 16h38

Reste a savoir si le traitement du test sera moins penalisant que l'update complet... a voir

Bon courage

**zoharcryss** · 18/08/2009, 09h56

J'ai une petite question sur la clause WHERE :

Code :

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

1
2
3
4
5
6
7
WHERE 
    mjoba.Job_idJob = tmp.idJob AND (
    mjoba.Quantite <> tmp.Qte OR
    mjoba.NomJob <> tmp.NomJob OR
    mjoba.Date <> tmp.Annee OR
    mjoba.Moyenne <> tmp.Moyenne
  )

La traduction de celle-ci est-elle :
On execute l'UPDATE dans les cas ou les IDs sont égaux ET (les quantités sont différentes OU les noms sont différents OU les années sont différentes OU les moyennes sont différentes)

Ma question est, dans le cas d'une ligne de la table MJOBA inexistante, est-ce que la nouvelle ligne calculée dans la table temporaire sera inséré dans la table MJOBA ?

Est-ce que les lignes de la table MJOBA comme ci dessous seront updatées par les nouvelles lignes de la table Tmp ? :

Table MJOBA

Job_idJob Quantite NomJob Date Moyenne
6400 1 PROCZ-100A-010D 2009 00:00:10

Par -->

Table Tmp

Job_idJob Quantite NomJob Date Moyenne
6400 2 PROCZ-100A-010D 2009 00:00:12

Dans le cas ou il y aurait de nouveaux IDJOBs j'aimerais qu'il les INSERT automatiquement car si j'ai bien compris dans le cas de cette requête il se contente que de la mise à jour des données existante dans la table MJOBA mais pas des nouvelles calculées dans la table TMP...

Merci d'avance

**zoharcryss** · 18/08/2009, 10h50

J'ai pensé à un truc, je ne sais pas si c'est judicieux de faire ça et je ne sais même pas comment le faire mais si on met une condition NOT EXISTS INSERT est-ce que ça fonctionnerait et quelle tête ça aurait ??

**CinePhil** · 18/08/2009, 11h08

J'ai une petite question sur la clause WHERE :

Code :
WHERE
mjoba.Job_idJob = tmp.idJob AND (
mjoba.Quantite <> tmp.Qte OR
mjoba.NomJob <> tmp.NomJob OR
mjoba.Date <> tmp.Annee OR
mjoba.Moyenne <> tmp.Moyenne
)
La traduction de celle-ci est-elle :
On execute l'UPDATE dans les cas ou les IDs sont égaux ET (les quantités sont différentes OU les noms sont différents OU les années sont différentes OU les moyennes sont différentes)

Bonne traduction.

Ma question est, dans le cas d'une ligne de la table MJOBA inexistante, est-ce que la nouvelle ligne calculée dans la table temporaire sera inséré dans la table MJOBA ?

Non puisque c'est un UPDATE et pas un INSERT.

Est-ce que les lignes de la table MJOBA comme ci dessous seront updatées par les nouvelles lignes de la table Tmp ? :

Table MJOBA
Citation:
Job_idJob Quantite NomJob Date Moyenne
6400 1 PROCZ-100A-010D 2009 00:00:10
Par -->

Table Tmp
Citation:
Job_idJob Quantite NomJob Date Moyenne
6400 2 PROCZ-100A-010D 2009 00:00:12

Oui puisque Quantité et Moyenne sont différents.

Dans le cas ou il y aurait de nouveaux IDJOBs j'aimerais qu'il les INSERT automatiquement car si j'ai bien compris dans le cas de cette requête il se contente que de la mise à jour des données existante dans la table MJOBA mais pas des nouvelles calculées dans la table TMP...

Exactement. Les nouveaux idJob ne seront pas insérés.

J'ai pensé à un truc, je ne sais pas si c'est judicieux de faire ça et je ne sais même pas comment le faire mais si on met une condition NOT EXISTS INSERT est-ce que ça fonctionnerait et quelle tête ça aurait ??

En cherchant rapidement dans la doc, je n'ai pas trouvé cette syntaxe pour un INSERT.
Par contre, j'y ai trouvé REPLACE qui serait peut-être bien pour ton cas.
A creuser.

**zoharcryss** · 18/08/2009, 11h18

Si je TRUNCATE ma table et qu'ensuite je me cantonne simplement à réinsérer la totalité de mes données de la table tmp est-ce que ça serait plus rapide dans la mesure ou je ne ferais aucune vérification ou comparaison?

Si c'est plus intéressant quelle serait la requête avec la syntaxe du INSERT.
Ce que j'ai pu trouver sur le INSERT c'est :

Code :

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

INSERT INTO Table (Champ1,champ2...) VALUES (...,...)

Comment imbriquer ma requête de calcul de moyenne dans ça

?

Pour le REPLACE la syntaxe est similaire à l'INSERT donc je ne sais pas non plus comment placer ma requête

!

**CinePhil** · 18/08/2009, 11h23

Si je TRUNCATE ma table et qu'ensuite je me cantonne simplement à réinsérer la totalité de mes données de la table tmp est-ce que ça serait plus rapide dans la mesure ou je ne ferais aucune vérification ou comparaison?

C'est bien possible en effet !

Si c'est plus intéressant quelle serait la requête avec la syntaxe du INSERT.

Tout simplement un INSERT SELECT :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO mjoba(Job_idJob, Quantite, NomJob, `Date`, Moyenne)
SELECT 
  Job.idJob,
  COUNT(Job_idJob) AS Qte,
  Job.NomJob,
  SUBSTR(Debut,1,4) AS Annee,
  SEC_TO_TIME(Avg(TIME_TO_SEC(Temp))) AS Moyenne
FROM DureeJob 
INNER JOIN Job ON DureeJob.Job_idJob = Job.idJob 
WHERE job.nomjob LIKE '%-%A-%'
GROUP BY 
  SUBSTR(DEBUT,1,4),
  job_idjob 
ORDER BY Job.NomJob ASC