데이터베이스 질문과 답변
by lgxj20 [2024.02.20 11:51:47]
안녕하세요
MERGE INTO를 사용해서
UPDATE, INSERT 를하려고하는데요 BB에 데이타가 N건이 나올수가 있습니다.
예시1). SQL
MERGE INTO TABLE_A AA
USING
(
SELECT .
FROM DUAL
) BB
ON
(
AA. = BB.
)
WHEN MATCHED THEN UPDATE SET
AA.COL1 = XX
.,AA.COL2 = YY
WHEN NOT MATCHED THEN
INSERT
(
AA.COL1
.....
)
VALUES
(
-- 이렇게 사용가능한가요?
SELECT ..
FROM TABLE_BBBBB CC
WHERE 1=1
AND CC.COL1 = BB.COL1
AND CC.COL2 = BB.COL2
)
우선 실행을 하면
ORA-06550 : ...
PL/SQL : ORA-00936
가발생을합니다.
위 처럼 사용이 불가능한걸까요??
아니면 예시2처럼
그냥 MERGE INTO를 사용해서
UPDATE만하고 INSERT 를 하려는데 UPDATE만 되고 있습니다.
예시2). SQL
MERGE INTO TABLE_A AA
USING
(
/**/
SELECT .
FROM DUAL
) BB
ON
(
AA. = BB.
)
WHEN MATCHED THEN UPDATE SET
AA.COL1 = XX
.,AA.COL2 = YY;
IF (SQL%ROWCOUNT = 0) THEN
INSERT ...실행
뭐가 문제가되는걸까요?
쿼리로 질문하지 마시고, 데이터로 질문해 주세요.
원본 대비 결과표.
원하는 결과 값을 엑셀로 정리해봤습니다.
1. conf_qty 는 B 의 컬럼인거죠?
2. 체크대상 수량은 항상 1행 인거죠?
3. 입력수량 합계와 체크수량을 비교하여
- 입력수량이 맞으면? 그대로 업데이트
- 입력수량이 모자라면? 추가로 인서트
- 입력수량이 넘치면? 어떻게 하나요? 체크수량에 맞도록 차감하여 업데이트?
1. conf_qty 는 B 의 컬럼인거죠?
: 네
2. 체크대상 수량은 항상 1행 인거죠?
: 네 체크 대상 테이블은 항상 1행입니다.
3. 입력수량 합계와 체크수량을 비교하여
- 입력수량이 맞으면? 그대로 업데이트
: 네
- 입력수량이 모자라면? 추가로 인서트
: 네
- 입력수량이 넘치면? 어떻게 하나요? 체크수량에 맞도록 업데이트?
: 네 체크수량에 맞도록만 업데이트 입니다.
하루종일 생각중인데 이게 되면 저게 안되고 그러네요 ㅜㅜ 3가지 조건으로 다 된야되는 상황이라..
CREATE TABLE test1
AS
SELECT 'A' sty, 'B1' col, 10 siz, 1 seq, 5 qty FROM dual
UNION ALL SELECT 'A', 'B2', 10, 1, 5 FROM dual
UNION ALL SELECT 'A', 'B3', 10, 1, 5 FROM dual
UNION ALL SELECT 'A', 'B4', 10, 1, 5 FROM dual
UNION ALL SELECT 'A', 'B5', 10, 1, 5 FROM dual
;
CREATE TABLE test2
AS
SELECT 'A' sty, 'B1' col, 10 siz, 1 seq, 5 qty, 0 conf_qty FROM dual
UNION ALL SELECT 'A', 'B2', 10, 1, 2, 0 FROM dual
UNION ALL SELECT 'A', 'B2', 10, 2, 1, 0 FROM dual
UNION ALL SELECT 'A', 'B2', 10, 3, 2, 0 FROM dual
UNION ALL SELECT 'A', 'B4', 10, 1, 2, 0 FROM dual
UNION ALL SELECT 'A', 'B4', 10, 2, 1, 0 FROM dual
UNION ALL SELECT 'A', 'B5', 10, 1, 3, 0 FROM dual
UNION ALL SELECT 'A', 'B5', 10, 2, 3, 0 FROM dual
UNION ALL SELECT 'A', 'B5', 10, 3, 3, 0 FROM dual
;
MERGE INTO test2 a
USING
(
SELECT a.sty, a.col, a.siz
, NVL(b.seq, a.seq) seq
, NVL(b.qty, a.qty) qty
, NVL(GREATEST(LEAST(a.qty, b.qty, a.qty - b.s + b.qty), 0), a.qty) conf_qty
FROM test1 a
LEFT OUTER JOIN
(SELECT sty, col, siz, seq, qty
, SUM(qty) OVER(PARTITION BY sty, col, siz ORDER BY seq) s
FROM test2
) b
ON a.sty = b.sty
AND a.col = b.col
AND a.siz = b.siz
UNION ALL
SELECT a.sty, a.col, a.siz
, MAX(b.seq) + 1 seq
, a.qty - SUM(b.qty) qty
, a.qty - SUM(b.qty) conf_qty
FROM test1 a
LEFT OUTER JOIN test2 b
ON a.sty = b.sty
AND a.col = b.col
AND a.siz = b.siz
GROUP BY a.sty, a.col, a.siz, a.qty
HAVING a.qty > SUM(b.qty)
ORDER BY 1, 2, 3, 4
) b
ON (a.sty = b.sty
AND a.col = b.col
AND a.siz = b.siz
AND a.seq = b.seq
)
WHEN MATCHED THEN
UPDATE SET conf_qty = b.conf_qty
WHEN NOT MATCHED THEN
INSERT VALUES (b.sty, b.col, b.siz, b.seq, b.qty, b.conf_qty)
;
SELECT *
FROM test2
ORDER BY sty, col, siz, seq
;
감사합니다.
이렇게 하면 되는군요...역시 많이 배우게 됩니다.^^
) 버튼을 클릭하여 작성 하시면 됩니다.