Bonjour

L’objectif de ma requête est de faire un update ou un insert a un numero de telephone avec um merge.
Le probleme c’est que j’ai une erreur du tipe “ora-38104 columns referenced in the on clause cannot be update” car dans ma clause on j'utilise la colonne telephone (colonne que je veut updater ).

Vous avez une idée de comment faire, ce merge sans retirer la colonne telephone de ma clause on?

requête:


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
MERGE INTO   telephones p
    USING (SELECT p_tel_id AS tel_id, 2 AS tip_tel, p_mobile_phone   AS n_telephone FROM dual WHERE p_mobile_phone   IS NOT NULL     --Telemóvel Principal
            UNION 
           SELECT p_tel_id AS tel_id, 1 AS tip_tel, p_personal_phone AS n_telephone FROM dual WHERE p_personal_phone IS NOT NULL   -- Principal Residência
            UNION 
           SELECT p_tel_id AS tel_id, 6 AS tip_tel, p_work_phone     AS n_telephone FROM dual WHERE p_work_phone     IS NOT NULL    --Trabalho
            UNION 
           SELECT p_tel_id AS tel_id, 5 AS tip_tel, p_fax_number     AS n_telephone FROM dual WHERE p_fax_number     IS NOT NULL) b  --Nº Fax
     --      
       ON (p.dni = b.dni AND p.n_telephone = b.n_telephone) 
     WHEN MATCHED THEN 
          UPDATE SET p.n_telephone =  b.n_telephone  
           WHERE p.tel_id  = b.tel_id
             AND p.tip_tel = b.tipo_tel 
     WHEN NOT MATCHED THEN
     --
   INSERT  VALUES (b.tel_id,b.tip_tel,b.n_telephone,'N',NULL,NULL);