Discussion :

[StringBuilder]

Bonjour,

J'ai le problème habituel lorsqu'on désire mettre à jour un jeu de lignes d'une table dont la sélection et les valeurs à mettre à jour dépendent d'une jointure avec une autre table.

Avec d'autres SGBD, on utilisera la clause "FROM" du UPDATE, ou "INNER JOIN".
Mais Oracle ne semble toujours pas supporter ce genre de requête.

J'ai trouvé en lieu et place, sur le lien ci-dessous qu'on pouvait mettre à jour le résultat d'un sous-select.
Seulement, dans mon cas, le sous-select contient une jointure, et c'est le drame.
http://geekswithblogs.net/WillSmith/...oin-again.aspx

Ma requête :

Code sql :

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

1
2
3
4
5
6
7
8
9
 
update 
(
  select eve.codsoc, eve.achvte, eve.typeve, eve.numeve, eve.utimod, eve.datmod, eve.dateve, jnl.datecr
  from eve
  inner join jnl on jnl.codsoc = eve.codsoc and jnl.achvte = eve.achvte and jnl.typeve = eve.typeve and jnl.numeve = eve.numeve and jnl.typecr = 'CLI'
) t
set t.utimod = 'DES', t.datmod = to_char(sysdate, 'YYYYMMDD'), t.dateve = t.datecr
where t.codsoc = 100 and t.achvte = 'V' and t.typeve = 'FAC' and t.dateve <> t.datecr;

L'erreur :

Erreur à la ligne de commande : 7, colonne : 4
Rapport d'erreur :
Erreur SQL : ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.

Ce que je cherche à faire :

Dans la table EVE, je souhaite mettre à jour la colonne DATEVE avec la valeur DATECR de la table JNL pour toutes les lignes qui respectent la jointure sur CODSOC/ACHVTE/TYPEVE/NUMEVE et un filtre sur JNL.TYPECR = 'CLI', pour lesquelles les deux dates sont différentes.

Comment faire ?

J'ai bien la solution de faire deux fois la même sous-requête (une fois pour récupérer la date à mettre à jour, et une fois pour le filtre), mais niveau maintenance, ça va être la tannée...

Oracle 11gR2

[StringBuilder]

Faute de mieux, j'ai utilisé cette requête usine à gaz :

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
 
update eve
set eve.utimod = 'DES', eve.datmod = to_char(sysdate, 'YYYYMMDD'), eve.dateve = (
  select jnl.datecr
  from jnl
  where jnl.codsoc = eve.codsoc 
  and jnl.achvte = eve.achvte 
  and jnl.typeve = eve.typeve 
  and jnl.numeve = eve.numeve 
  and jnl.typecr = 'CLI'
)
where (eve.codsoc, eve.achvte, eve.typeve, eve.numeve) in (
  select eve.codsoc, eve.achvte, eve.typeve, eve.numeve
  from eve
  inner join jnl on jnl.codsoc = eve.codsoc and jnl.achvte = eve.achvte and jnl.typeve = eve.typeve and jnl.numeve = eve.numeve and jnl.typecr = 'CLI'
  where eve.codsoc = 100
  and eve.achvte = 'V'
  and eve.typeve = 'FAC'
  and eve.dateve <> jnl.datecr
);

Vive Oracle

[mnitu]

En général les solutions à ce type de problème en SQL sont :

1. Utilisation des sous-requêtes corrélées
2. Utilisation du Merge
3. Utilisation d’une jointure via une vue inline (ad-hoc)

Chaque méthode est adaptée à certaines situations et les méthodes 2 et 3 ont certaines restrictions. Cherchez des exemples sur ce forum.

[Waldar]

1. Utilisation des sous-requêtes corrélées
2. Utilisation du Merge
3. Utilisation d’une jointure via une vue inline (ad-hoc)

Tout à fait d'accord avec mnitu.
Je réponds seulement pour vous donner un discret indice sur ma préférence.

[StringBuilder]

J'aime bien ta notion de "discret"



Ok, je vais regarder la clause MERGE. C'est standard ?

[mnitu]

« Merge mon amour » a des restrictions donc quand ça ne marche pas …

Et pour nuancer un peu ce n’est pas parce que dans la plupart des cases MERGE surpasse les autres méthodes en termes de performance qu’il faut toujours l’employer.

[thepierre]

j'ai essayé de constuire ton merge, un peu à l'aveuglette...

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
merge into eve cib
using (SELECT eve.rowid rid,
              eve.codsoc, 
              eve.achvte, 
              eve.typeve, 
              eve.numeve, 
              eve.utimod, 
              eve.datmod, 
              eve.dateve, 
              jnl.datecr
  FROM eve
  INNER JOIN jnl ON jnl.codsoc = eve.codsoc AND jnl.achvte = eve.achvte 
  AND jnl.typeve = eve.typeve AND jnl.numeve = eve.numeve AND jnl.typecr = 'CLI') vw
  on(
  cib.codsoc = vw.codsoc and cib.achvte=vw.achvte and cib.typeve=vw.typeve and cib.numeve=vw.numeve and
  vw.codsoc = 100 AND vw.achvte = 'V' AND vw.typeve = 'FAC')
when matched then update set  cib.utimod='DES',
                              cib.datmod=to_char(sysdate, 'YYYYMMDD'),
                              cib.dateve=vw.datecr where t.dateve <> t.datecr

merci pour ton retour.