Update d'un champ obtenu par sous-requête [Résolu]

[juju05]

Bonjour,

Je travaille sous Oracle 10g. Je cherche à mettre à jour le prix d'inventaire pour les articles correspondant à "un certain type".

Voici ma requête pour obtenir ce prix:

Code :

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
SELECT a.id_article, z.cout_total
FROM toppdm.object o 
INNER JOIN (SELECT tmp.id_routing, sum(tmp.cout_pour_un) AS cout_total
FROM
((SELECT b1.*
  FROM V_TRT_UNIT_BESOIN_ACHAT b1
 union
 SELECT b2.*
 FROM V_TRT_UNIT_CDE_BES_ACHAT b2
 union 
 SELECT b3.*
 FROM V_TRT_UNIT_BES_CDE_ACHAT b3
 union
 SELECT b4.*
 FROM V_TRT_UNITTOT_BESOIN_ACHAT b4
 union 
 SELECT b5.*
 FROM V_TRT_UNITTOT_BESAB1_ACHAT b5
 union
 SELECT b6.*
 FROM V_TRT_UNITTOT_BESAB2_ACHAT b6
 union
 SELECT b7.*
 FROM V_TRT_UNIT_TOLE_1 b7 
 union
 SELECT b8.*
 FROM V_TRT_UNIT_TOLE_2 b8
 union
 SELECT b9.*
 FROM V_TRT_UNIT_TOLE_3 b9 
 union
 SELECT b10.*
 FROM V_TRT_UNIT_TOLE_4 b10
 union
 SELECT b11.*
 FROM V_TRT_UNIT_TOLE_5 b11
 union 
 SELECT b12.*
 FROM V_TRT_BES_M_MM b12
 union
 SELECT b20.*
 FROM V_TRT_MAT_SANS_LIV b20
) tmp)
GROUP BY tmp.id_routing) z
ON o.id_routing = z.id_routing 
INNER JOIN toperp.t_article a
ON a.id_object = o.id_object
WHERE a.b_prod = -1
  AND a.id_famille = 385
  AND a.id_type_article = 1
  AND o.type = 47

Je souhaite mettre à jour le champ pu de la table t_article à partir de z.cout_total. L'identifiant est id_article.
Je ne connais pas la syntaxe à adopter.

Merci d'avance.
Julien.

[boussafi]

en resumé:

Code :

UPDATE T_ARTICLE ART SET PU=(SELECT cout_total FROM  ( ta_requete ) WHERE ART.id_article=ta_requete.id_article)

en détail:

Code :

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
52
53
54
UPDATE T_ARTICLE ART SET PU=(SELECT cout_total FROM  ( --nouvelle ligne
SELECT a.id_article, z.cout_total
FROM toppdm.object o 
INNER JOIN (SELECT tmp.id_routing, sum(tmp.cout_pour_un) AS cout_total
FROM
((SELECT b1.*
  FROM V_TRT_UNIT_BESOIN_ACHAT b1
 union
 SELECT b2.*
 FROM V_TRT_UNIT_CDE_BES_ACHAT b2
 union 
 SELECT b3.*
 FROM V_TRT_UNIT_BES_CDE_ACHAT b3
 union
 SELECT b4.*
 FROM V_TRT_UNITTOT_BESOIN_ACHAT b4
 union 
 SELECT b5.*
 FROM V_TRT_UNITTOT_BESAB1_ACHAT b5
 union
 SELECT b6.*
 FROM V_TRT_UNITTOT_BESAB2_ACHAT b6
 union
 SELECT b7.*
 FROM V_TRT_UNIT_TOLE_1 b7 
 union
 SELECT b8.*
 FROM V_TRT_UNIT_TOLE_2 b8
 union
 SELECT b9.*
 FROM V_TRT_UNIT_TOLE_3 b9 
 union
 SELECT b10.*
 FROM V_TRT_UNIT_TOLE_4 b10
 union
 SELECT b11.*
 FROM V_TRT_UNIT_TOLE_5 b11
 union 
 SELECT b12.*
 FROM V_TRT_BES_M_MM b12
 union
 SELECT b20.*
 FROM V_TRT_MAT_SANS_LIV b20
) tmp)
GROUP BY tmp.id_routing) z
ON o.id_routing = z.id_routing 
INNER JOIN toperp.t_article a
ON a.id_object = o.id_object
WHERE a.b_prod = -1
  AND a.id_famille = 385
  AND a.id_type_article = 1
  AND o.type = 47
 
  ) WHERE ART.id_article=ta_requete.id_article)--nouvelle ligne

[juju05]

Merci beaucoup.