?????. DB2 ? XpertMon ??? ???!
? ????????? DB ??????.
??? ????? V8.1 (Fixpak2 ??) ? ?? ??? SQL ?? ??? MERGE ???
??? ??? ?????.
?? ????? ???, ???? ?? ?? ???? ??? ??? ??? ??????.
???? ????? ?????.
MERGE Statement
? ????? ?? ???? ???? ??? ?? ??? ???????
?? ??, ????? ??? 2? ?? SQL ?? ?????? ???, DB2 UDB V8 FP2
????? MERGE STATEMENT ? ???? ??? ?? ? ? ????.
??? ??? ? ????? ?? ???? ???? ?? ? ?? ???? ???.
??? ?? ??? ?? ???? ??? ?????? ?????.
TEST ??
- DB : SAMPLE
- ???:
- total : ????? ?? ????? ???? ???
- ID
- ??
- ????
- ?? ID? ??? ??? ? ??
- ? ?? ??
- daily : ????? ????? ???? ???
- ???? : daily ???? ??? total ???? ???.
?? ID ? ????, ?? ID ? ?? ??? ??? Update ??, ID ? ???? ???
Total ? Insert? ??.
- ???? : Merge ??? ??. daily ???? total ????? matching ???
non-matching ??? ???? ????.
?? ??? ????? WHEN MATCHED THEN ??? SQL ? ????, ???? ???
??? WHEN NOT MATCHED THEN ??? SQL ? ????.
TEST ? ??? SQL
- ??? ?? SQL
create table total(id int,name char(10),zipcode char(10),num_of_trans int,amount decimal(6,2))
create table daily (id int, name char(10), zipcode char(10), amount int)
- ??? ?? SQL
insert into total values (1, ‘Helen’, ‘123456’, 3, 100.00)
insert into total values (2, ‘Fred’, ‘234567’, 1, 20.00)
insert into daily values (1, ‘Helen’, ‘123456’, 10.00)
insert into daily values (3, ‘Sue’, ‘987654’, 50.00)
- ??? ?? SQL
select * from total
ID NAME ZIPCODE NUM_OF_TRANS AMOUNT
---- ----------- ----------- --------------- -------
1 Helen 123456 3 100.00
2 Fred 234567 1 20.00
select * from daily
ID NAME ZIPCODE AMOUNT
---- ------------ --------- ---------
1 Helen 123456 10
3 Sue 987654 50
- MERGE SQL
?? ID ??? ????? ?? ??? num_of_trans ??? ????? ????,
????? ????, ???? ???? ??? ??? insert ??.
- ?? ?? SQL
ID 1? Update, ID 3? Insert ???? ? ? ??.
- MERGE ?? ? ???? ??? ??? ????? ???? ?? daily ? select
XpertMon for DB2 UDB V2
DB ???
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail : hjlee@iteg.co.kr
URL : http://iteg.co.kr
|
|
|