Db2/KDUG 2013 Q·A
Merge with CTE(Common Table Expression) 사용 가능여부
@ipajama
2013. 12. 23. 14:39
728x90
윈드밀님 | 쿼리 | 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 t
using ORGAN as s
on(t.c1=s.lvl)
when matched then
update set
c1=s.lvl
when 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
;
| |||||||||
| |||||||||
| |||||||||
| |||||||||
728x90