Äõ¸® °øºÎ ÇÏ°í Àִµ¥ ¾Æ·¡ ¹®Á¦°¡ ÀÌÇØ°¡ µÇÁö ¾Ê¾Æ Á¶¾ðÁ» ºÎŹµå¸³´Ï´Ù.
¹®Á¦´Â ¸ÓÁö ±¸¹® ¼öÇàÈÄ A Å×À̺íÀÇ ÃÖÁ¾ °Ç¼ö¸¦ ±¸ÇÏ´Â ¹®Á¦ÀÔ´Ï´Ù.
drop table A;
drop table B;
create table A (
col1 varchar(2),
col2 varchar(4),
col3 number );
create table B (
col1 varchar(2),
col2 varchar(4),
col3 number );
insert into A values ('a', 'x', 1);
insert into A values ('b', 'y', 2);
insert into A values ('c', 'z', 3);
insert into B values ('a', 'x', 1);
insert into B values ('b', 'y', 2);
insert into B values ('c', 'z', 3);
insert into B values ('d', '°¡', 4);
insert into B values ('e', '³ª', 5);
commit;
merge into A
using B
on (A.col1 = B.col1)
when matched then
update set A.col3 = 4
where A.col3 =2
delete where A.col3 <= 2
when not matched then
insert (A.col1, A.col2, A.col3) values (B.col1, B.col2, B.col3)
;
¿©±â¿¡¼ ¿Ö 5°ÇÀÌ ³ª¿À´ÂÁö ±×¸²À» ±×·Á°¡¸é¼ Çصµ ÀÌÇØ°¡ µÇÁö ¾Ê½À´Ï´Ù.
óÀ½ A Å×À̺íÀÇ (a,x,1) °ªÀÌ Á¶ÀÎ Á¶°Ç¿¡ ÀÇÇØ (on A.col1 = B.col1) ¸ÅÄ¡°¡ µÇ¹Ç·Î (b,y,2)ÀÇ °ªÀÌ (b,y,4)°¡ µÇ°í delete Á¶°Ç¿¡ ÀÇÇØ (a,x,1) Á¶°ÇÀÌ »èÁ¦
µû¶ó¼ ÇöÀç A Å×ÀÌºí¿¡´Â (b,y,4)¿Í (c,z,3) 2°³ÀÇ ÇàÀÌ Á¸Àç
´ÙÀ½ A Å×À̺íÀÇ (b,y,4) °ªÀÌ Á¶ÀÎ Á¶°Ç¿¡ ÀÇÇØ ¸ÅÄ¡°¡ µÇÁö¸¸ ¸ÅÄ¡°¡ µÇ¾úÀ» ¶§ ¼öÇàµÇ¾î¾ß ÇÒ Äõ¸®¿¡ ¸Â´Â Á¶°ÇÀÌ ¾øÀ¸¹Ç·Î ±×´ë·Î 2°³ÀÇ ÇàÀÌ À¯Áö
±×¸®°í ¸¶Áö¸· ÇàÀÌ (c,z,3) ¿ª½Ã Á¶ÀÎ Á¶°Ç¿¡ ÀÇÇØ ¸ÅÄ¡°¡ µÇÁö¸¸ ¸ÅÄ¡°¡ µÇ¾úÀ» ¶§ ¼öÇàµÇ¾î¾ß ÇÒ Äõ¸®¿¡ ¸Â´Â Á¶°ÇÀÌ ¾øÀ¸¹Ç·Î ±×´ë·Î 2°³ Çà À¯Áö
µû¶ó¼ ÃÖÁ¾ÀûÀ¸·Î AÀÇ Å×À̺íÀÇ °Ç¼ö´Â (b,y,4),(c,z,3) 2°Ç
Àú´Â À§Ã³·³ ÀÌÇØ°¡ µÇ¾î¼¿ä.
Àü¹®°¡ ºÐµéÀÇ ¼³¸í Á» ºÎŹµå¸®°Ú½À´Ï´Ù.
°¨»çÇÕ´Ï´Ù.
|
ÀÌÂü¿¡ È®ÀÎÇغ¸´Ï delete´Â update µÇ´Â row ÇÑÁ¤Çؼ Ãß°¡·Î Á¶°ÇÀ» È®ÀÎÇÏ´Â °Å³×¿ä.
update set A.col3 = 4 where A.col3 =2 ¿¡¼ col3 ÀÌ 2Àΰ͵é Áß¿¡¼ ¸¸ delete Á¶°ÇÀ» º¸´Â°Ì´Ï´Ù.
±×·±µ¥ col3À» 4·Î ¹Ù²ã¹ö¸®´Ï delete Á¶°Ç¿¡ ¾È°É¸®´Â°ÅÁö¿ä.
update set A.col3 = 1 where A.col3 =2 ·Î Çؼ delete Á¶°Ç¿¡ col3ÀÌ °É¸®µµ·Ï Çϸé Áö¿öÁý´Ï´Ù.