윈드밀님 | 쿼리 | 2013-01-11 18:31:28
PMR을 했는데요. 여기도 올려봅니다.
그리고 뒤에 local fix를 만든 분이 65살(IBM 정년 퇴임후 계속 일하고 있음.일본) 이란 것도 감동입니다.우리도 그렇게 될 수 있을까요?테스트 테이블 : create table xxx (c1 int);[Fail SQL]WITH ORGAN(LVL)AS ( SELECT 1 AS LVL FROM sysibm.sysdummy1 I UNION ALL SELECT P.LVL+1 FROM ORGAN P , sysibm.sysdummy1 C WHERE P.LVL<=10 )merge into xxx as tusing ORGAN as son(t.c1=s.lvl)when matched then update set c1=s.lvlwhen not matched then insert (c1) values (s.lvl);[Local Fix SQL]WITH ORGAN(LVL)AS ( SELECT 1 AS LVL FROM sysibm.sysdummy1 A UNION ALL SELECT B.LVL+1 FROM ORGAN B , sysibm.sysdummy1 C WHERE B.LVL<=10 )/*WHEN MATCHED 관련 UPDATE문을 수행하는 WITH SELECT문 */, MATCHED(UPDATED_ROWS)AS( SELECT COUNT(*) AS UPDATED_ROWS FROM FINAL TABLE ( UPDATE xxx DD SET DD.c1 = ( SELECT AAA.LVL FROM ORGAN AAA WHERE AAA.LVL = DD.c1 ) WHERE EXISTS ( SELECT BBB.LVL FROM ORGAN BBB WHERE BBB.LVL = DD.c1 ) ) D)/*WHEN NOT MATCHED 관련 INSERT문을 수행하는 WITH SELECT문 */, NOT_MATCHED(INSERTED_ROWS)AS( SELECT COUNT(*) AS INSERTED_ROWS FROM FINAL TABLE ( INSERT INTO xxx (c1) SELECT EE.LVL FROM ORGAN EE WHERE NOT EXISTS ( SELECT * FROM xxx CCC WHERE CCC.c1 = EE.LVL ) ) E)SELECT * FROM MATCHED , NOT_MATCHED;