Utilisation de l'instruction MERGE

Version imprimable

Bonjour à tous,

Je souhaite simplement avoir une information. Je viens d'avoir connaissance de l'instruction MERGE et j'aimerais savoir si je peux l'utiliser de la façon suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
--pour initialiser le code contrat 0715, on concatène à 0715 les derniers chiffres du code créance 0312 correspondant
            SET DecCodeContrat715 = '0715'||substr(DecCodeContrat312_C2,5,16);
 
            MERGE INTO decompte_poste D1
                USING (select dec_code_contrat, dec_code_poste, dec_type_decompte, dec_code_client, dec_mt_poste, dec_mt_restant, 
                dec_type_tiers, dec_code_tiers FROM decompte_poste) D2
                ON (D1.dec_code_contrat = DecCodeContrat715
                AND D1.dec_code_poste = DecCodePoste)
            WHEN MATCHED THEN
                UPDATE SET D1.dec_mt_poste = D1.dec_mt_poste + DecMtPoste, D1.dec_mt_restant = D1.dec_mt_restant + DecMtRestant;
            WHEN NOT MATCHED THEN
                UPDATE SET D1.dec_code_contrat = DecCodeContrat715;

En gros, je voudrais savoir si je peux exécuter un UPDATE dans la clause 'WHEN NOT MATCHED' au lieu d'un INSERT.

11/09/2012, 11h32
Waldar

Non, et ça n'aurait d'ailleurs aucun sens.
Le WHEN NOT MATCHED signifie qu'Oracle n'a pas trouvé de correspondance entre les lignes sources et cibles.
Dès lors, quelle ligne devrait-il mettre à jour ?

Ce que vous voulez faire je pense c'est un simple UPDATE non ?
11/09/2012, 12h20
Rei Ichido

WHEN NOT MATCHED signifie que la source n'a pas trouvé de correspondance pour la cible ; SQL Server a introduit la condition inverse (WHEN NOT MATCHED BY SOURCE), ce qui a quand même du sens intuitivement : si je veux comparer deux ensembles, je vais bien avoir des correspondances, et des éléments présents dans le 1er mais pas de le 2nd d'une part, et dans le 2nd mais pas dans le 1er d'autre part.
Cela dit, ce n'est (à ma connaissance) pas possible directement sous Oracle !

=> Il faudra faire un UPDATE ... WHERE NOT EXISTS ...
11/09/2012, 14h11
Waldar

Je ne vois toujours pas comment mettre à jour une ligne qu'on ne trouve pas.
11/09/2012, 14h56
mnitu

De tout façon il faut que @merlubreizh lise la doc pour MERGE et quelques exemples d’utilisation, parce que sa manière de l’employer montre quelques problèmes de compréhension de cette instruction.
11/09/2012, 15h11
Rei Ichido

Citation:

Envoyé par Waldar

Je ne vois toujours pas comment mettre à jour une ligne qu'on ne trouve pas.

Soit l'expression :

MERGE INTO cible USING source ON (condition)

J'ai 3 cas :
1. Une ligne commune aux deux tables, avec la condition respectée : je fais en général un UPDATE.
2. J'ai une ligne de source qui n'a pas de correspondance dans cible (au sens de "condition") : je fais en général un INSERT.
3. Cas non traité par Oracle : j'ai une ligne de cible qui n'a pas de correspondance dans source (toujours au sens de "condition"). Et là, merlubreizh souhaiterait faire un UPDATE.

http://technet.microsoft.com/fr-fr/l.../bb510625.aspx

On peut imaginer le scénario suivant :
- Une table "client" (cli_id, cli_sigle, cli_nom, cli_naissance, commentaire)
- Une table "changements" (cli_signel, cli_nom, cli_naissance)

La table "client" est le référentiel client.
La table "changements" est alimentée par une interface qui envoie des modifications sur les clients : ajouts, mises à jour.

Contenu de la table "client" :

Code:

1
2
3
4
5
6
7
8
9
 
cli_id      cli_sigle cli_nom                        cli_naissance commentaire
----------- --------- ------------------------------ ------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           000001    Durant                         1980-12-28    
2           000002    Client 2                       1970-01-15    
3           000003    Dupuis                         1990-03-20    
4           000004    Client 4                       1990-06-25    
 
(4 row(s) affected)

Table "changements" :

Code:

1
2
3
4
5
6
7
 
cli_sigle cli_nom                        cli_naissance
--------- ------------------------------ -------------
000002    Dupont                         NULL
000005    Client 5                       1956-04-25
 
(2 row(s) affected)

Un merge :

Code:

1
2
3
4
5
6
7
8
9
10
 
merge client t
using (select cli_sigle, cli_nom, cli_naissance from changements) s
on s.cli_sigle = t.cli_sigle
when matched then
	update set t.cli_nom = coalesce(s.cli_nom, t.cli_nom), t.cli_naissance = coalesce(s.cli_naissance, t.cli_naissance), t.commentaire = t.commentaire + 'Mis à jour le ' + cast(getdate() as varchar)
when not matched by source and t.cli_nom like 'Client %' then
	update set t.commentaire = 'Non mis à jour le ' + cast(getdate() as varchar) + '\n'
when not matched by target then
	insert (cli_sigle, cli_nom, cli_naissance, commentaire) values (s.cli_sigle, s.cli_nom, s.cli_naissance, 'Ajouté le ' + cast(getdate() as varchar));

=> Quand on trouve le même sigle, on met à jour le nom et la date de naissance s'ils sont rempli
=> Quand on ne trouve pas dans la table TARGET, on ajoute le client
=> Quand on ne trouve pas dans la table SOURCE et que le client a le nom générique "Client x", on indique dans les commentaires qu'on n'a pas mis à jour la ligne

Résultat :

Code:

1
2
3
4
5
6
7
8
9
10
 
cli_id      cli_sigle cli_nom                        cli_naissance commentaire
----------- --------- ------------------------------ ------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           000001    Durant                         1980-12-28    
2           000002    Dupont                         1970-01-15    Mis à jour le Sep 11 2012  3:16PMMis à jour le Sep 11 2012  3:19PM
3           000003    Dupuis                         1990-03-20    
4           000004    Client 4                       1990-06-25    Non mis à jour le Sep 11 2012  3:19PM\n
5           000005    Client 5                       1956-04-25    Ajouté le Sep 11 2012  3:19PM
 
(5 row(s) affected)

11/09/2012, 15h41
merlubreizh

Bonjour et merci pour vos différents retours

Je vous explique grosso modo ce que j'essaye de faire dans ma table decompte_poste. Dans cette table, j'ai des champs dec_code_contrat (chaine) et dec_code_poste (chaine) qui constituent la clé primaire. Dec_code_contrat contient des codes de créances dont il faut que je modifie une partie seulement de la chaine.

Les 4 premiers chiffres du code créance correspondent au code agence, les autres chiffres au code client. Or, deux des agences ont fusionné (code agence 0000 et code agence 1111). L'agence 1111 a absorbé l'agence 0000. Les clients possèdent le même numéro dans les deux agences.

Seulement, pour une créance 0000xxx donnée et pour un code poste donné, il se peut que la créance 1111xxx avec le même code poste existe déjà, mais avec des montants dec_mt_poste et dec_mt_restant différents.

Ma logique est donc la suivante. Pour chaque créance 0000xxx et pour chaque code poste, je recherche si la créance 1111xxx correspondante avec le même code poste existe. Si c'est le cas, je souhaite mettre à jour les champs dec_mt_restant et dec_mt_poste de la créance 1111xxx sur ce code poste, puis supprimer l'enregistrement courant. Si l'enregistrement courant n'existe pas, je souhaite mettre à jour le code créance, qui ne commencera plus par '0000' mais par '1111'.

Exemple

dec_code_contrat : 00001298502
dec_code_poste : 80
dec_mt_poste : 1200.35
dec_mt_restant : 1200.35

Je recherche si le code 11111298502 avec le code poste 80 existe déjà. Si c'est le cas, je mets à jour les montants sur ce code 11111298502 et code poste 80. S'il n'existe pas, je mets à jour le code contrat.

PS : je précise que la base de données est de type DB2 et j'utilise SQL Developer comme éditeur.

Si je comprends bien vous voulez modifier une des zones qui constitue la clé primaire de votre table ce qui est une contradiction en soit parce que une clé primaire est immuable ! C’est comme dans la vraie vie : chaque fois quand vous vous tirez une balle dans les pieds c’est douloureux.

De plus avec une modélisation qui s’apparenté au cobol plutôt qu’au modèle relationnel : « clé intelligentes » le deuxième pied ne se sent pas bien non plus.

Si cette situation est exceptionnelle vous pouvez récréer la table qui contiendra vos modifications. Si non vous avez un gros problème de modélisation: licencié l’analyste !

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> With Data As (
  2    Select '00001298502' As dec_code_contrat,
  3           '80'          As dec_code_poste,
  4           1200.35       As dec_mt_poste,
  5           1200.35       As dec_mt_restant
  6      From dual
  7    Union ALL
  8    Select '00001298500' As dec_code_contrat,
  9           '80'          As dec_code_poste,
 10           5000          As dec_mt_poste,
 11           4000          As dec_mt_restant
 12      From dual
 13    Union ALL
 14    Select '11111298502' As dec_code_contrat,
 15           '80'          As dec_code_poste,
 16           100           As dec_mt_poste,
 17           80            As dec_mt_restant
 18      From dual
 19  ),
 20  Rel_Data As (
 21    Select Case SubStr(dec_code_contrat,1,4)
 22              When '0000' Then '1111'
 23              Else SubStr(dec_code_contrat,1,4)
 24           End Agence,
 25           SubStr(dec_code_contrat,5) Creance,
 26           dec_code_poste,
 27           dec_mt_poste,
 28           dec_mt_restant
 29      From data
 30  )
 31  Select Agence||Creance As dec_code_contrat,
 32         dec_code_poste,
 33         Sum(dec_mt_poste),
 34         Sum(dec_mt_restant)
 35    From Rel_Data
 36  Group By Agence||Creance, dec_code_poste
 37  /
 
DEC_CODE_CONTRAT DEC_CODE_POSTE SUM(DEC_MT_POSTE) SUM(DEC_MT_RESTANT)
---------------- -------------- ----------------- -------------------
11111298500      80                          5000                4000
11111298502      80                       1300,35             1280,35
 
SQL>

12/09/2012, 10h57
Waldar

Merci StringBuilder pour l'exemple côté SQL-Server, je n'avais pas creusé l'aspect le BY SOURCE / BY TARGET.
Côté performances je me dis que le SGBD doit faire un FULL OUTER JOIN au lieu d'un LEFT OUTER JOIN et ça doit avoir un impact sur les performances, mais pourquoi pas après tout.
12/09/2012, 14h18
Peut-êtreUneRéponse

NB : Les upserts selon les SGDBR ne sont pas gérés de la même manière et ne donnent pas les mêmes résultats. Les exemples ci-dessus me semblent faire référence à Oracle ou SQL-Server. Peut-être que ce thread devrait être déplacé dans le forum approprié (pas DB2)?

Bonjour à tous et merci pour votre aide ! Je me suis finalement débloqué en opérant de la façon suivante :

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
select count(dec_code_contrat) into cpt
            from decompte_poste
            where dec_code_contrat = DecCodeContrat715
            and dec_code_poste = DecCodePoste
            and dec_type_decompte = DecTypeDecompte
            and dec_code_client = DecCodeClient
            and dec_type_tiers = DecTypeTiers
            and dec_code_tiers = DecCodeTiers;
 
            if(cpt = 1) then
                --l'enregistrement a été trouvé, on met à jour les montants et on supprime l'enregistrement 0312
                UPDATE decompte_poste
                SET dec_mt_poste = dec_mt_poste + DecMtPoste, dec_mt_restant = dec_mt_restant + DecMtRestant
                where dec_code_contrat = DecCodeContrat715
                and dec_code_poste = DecCodePoste
                and dec_type_decompte = DecTypeDecompte
                and dec_code_client = DecCodeClient
                and dec_type_tiers = DecTypeTiers
                and dec_code_tiers = DecCodeTiers;
 
                delete from decompte_poste
                where dec_code_contrat = DecCodeContrat312_C2
                and dec_code_poste = DecCodePoste
                and dec_type_decompte = DecTypeDecompte
                and dec_code_client = DecCodeClient
                and dec_type_tiers = DecTypeTiers
                and dec_code_tiers = DecCodeTiers;
            else
                --l'enregistrement n'a pas été trouvé, on met à jour le code créance
                UPDATE decompte_poste
                SET dec_code_contrat = DecCodeContrat715
                where dec_code_contrat = DecCodeContrat312_C2
                and dec_code_poste = DecCodePoste
                and dec_type_decompte = DecTypeDecompte
                and dec_code_client = DecCodeClient
                and dec_type_tiers = DecTypeTiers
                and dec_code_tiers = DecCodeTiers;
            end if;

En gros, si je trouve l'enregistrement, je mets à jour mes montants et si je ne le trouve pas, je mets à jour mon code contrat, ce que je souhaite faire. Et ça fonctionne ! Merci à tous ceux qui se sont penchés sur le problème :D