[SQL] [ORACLE] Data update VS proc sql update

Bonjour,

Deux solutions existent :

En pur SAS, tu dois dupliquer les lignes de set pour chaque variable à mettre à jour.

Code:

1
2
3
4
5
6
proc sql ;
   update master 
   set val1 = (select new.val1 from new where master.var1 = new.var1 and master.var2 = new.var2), 
       val2 = (select new.val2 from new where master.var1 = new.var1 and master.var2 = new.var2)
;
quit ;

ou en faisant du pass-trough en appelant du pur code Oracle :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
proc sql ;
   connect to oracle ( <info_de_connections> ) ;
   execute (
       UPDATE master
         SET ( master.val1, master.val2 ) =
             ( SELECT new.val1, new.val2
               FROM new
               WHERE new.var1 = master.var1 and
                     new.var2 = master.var2 )
             )
         WHERE EXISTS
         ( SELECT new.val1, new.val2
           FROM new
           WHERE new.var1 = master.var1 and
                 new.var2 = master.var2
         )
      ) by oracle ;
 
   disconnect from oracle ;
 
quit ;

Timarsu.