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 55 56 57 58 59 60 61 62 63 64 65
|
SQL> create table src (col1 number,col2 varchar2(5), col3 varchar2(20));
Table crÚÚe.
SQL> insert into src select '123' ,'TOTO','AAAAAA' from dual union
2 select '234','TITI','IIIIIIIII' from dual union
3 select '345','TATA','OOOOOOOOO' from dual union
4 select '456','TUTU','UUUUUUUUU' from dual;
4 ligne(s) crÚÚe(s).
SQL> commit;
Validation effectuÚe.
SQL> create table tgt as select * from src;
Table crÚÚe.
SQL> update src set col2=null;
4 ligne(s) mise(s) Ó jour.
SQL> update src set col3=null;
4 ligne(s) mise(s) Ó jour.
SQL> alter table tgt add constraint pk_tgt primary key (col1) using index;
Table modifiÚe.
1 merge INTO tgt a1
2 USING (SELECT col1,col2,col3 FROM src WHERE src.col1 IN (SELECT col1 FROM tgt)) a2
3 ON (a1.col1=a2.col1)
4 when matched then UPDATE
5 SET a1.col2 = a2.col2, a1.col3=a2.col3
6 when NOT matched then INSERT
7* (col1) VALUES (NULL)
SQL> /
4 lignes fusionnÚes.
VARCHAR2(20)
SQL> delete from tgt;
4 ligne(s) supprimÚe(s).
1 merge INTO tgt a1
2 USING (SELECT * FROM src WHERE src.col1 IN (SELECT col1 FROM tgt))a2
3 ON (a1.col1=a2.col1)
4 when matched then UPDATE
5 SET a1.col2 = a2.col2, a1.col3=a2.col3
6 when NOT matched then INSERT
7* (col1) VALUES (NULL)
SQL> /
0 lignes fusionnÚes.
SQL> |
Partager