|
Author's name: Frank Zhou Author's Email:zhou328@comcast.net |
Date written: 3
February 2007 Oracle version(s): 10.2 |
|
How do I replace the data of the “Target” Table with the data of the “Source” Table in a SQL Statement? |
|
The following SQL
pattern can be used to replace the data of the “Target” Table with the data of
the “Source” Table in a SQL Statement.
create table target as
select 1 as num, 'will_be_updated' as ch from dual
union all
select 2 as num, 'will_be_updated' as ch from dual
union all
select 20 as num,'will_be_deleted' as ch from dual
create table source as
SELECT level num, chr(ascii('A')+level-1) ch
from dual connect by level <= 5
SQL> select * from target;
NUM CH
---------- ---------------
1 will_be_updated
2 will_be_updated
20 will_be_deleted
SQL> select * from source;
NUM C
---------- -
1 A
2 B
3 C
4 D
5 E
MERGE INTO target tgt
USING
(SELECT src.num, src.ch, tgt.num as tgt_num, tgt.ch as tg_ch
FROM source src FULL OUTER JOIN target tgt
ON (src.num = tgt.num )
) src
ON ( src.tgt_num = tgt.num )
WHEN MATCHED
THEN
UPDATE SET tgt.ch =
src.ch
WHERE
NVL(tgt.ch, chr(0) ) != NVL(src.ch, chr(0))
DELETE WHERE src.num IS NULL
WHEN NOT MATCHED
THEN
INSERT ( tgt.num, tgt.ch)
VALUES ( src.num, src.ch);
The “Target” Table is identical to
the “Source” Table after execute the “Merge” SQL statement.
SQL> select * from target;
NUM CH
---------- ---------------
1 A
2 B
3 C
4 D
5 E
SQL> spool off;