데이터베이스 질문과 답변

머지 update, insert 문의 드립니다. 0 7 2,840

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 ...실행

뭐가 문제가되는걸까요?

by 마농 [2024.02.20 13:14:40]

쿼리로 질문하지 마시고, 데이터로 질문해 주세요.

원본 대비 결과표.

by lgxj20 [2024.02.20 14:48:37]

원하는 결과 값을 엑셀로 정리해봤습니다.

by 마농 [2024.02.20 16:11:09]

1. conf_qty 는 B 의 컬럼인거죠?
2. 체크대상 수량은 항상 1행 인거죠?
3. 입력수량 합계와 체크수량을 비교하여
 - 입력수량이 맞으면? 그대로 업데이트
 - 입력수량이 모자라면? 추가로 인서트
 - 입력수량이 넘치면? 어떻게 하나요? 체크수량에 맞도록 차감하여 업데이트?

by lgxj20 [2024.02.20 16:17:17]


1. conf_qty 는 B 의 컬럼인거죠?
     : 네

2. 체크대상 수량은 항상 1행 인거죠?
     : 네 체크 대상 테이블은 항상 1행입니다.

3. 입력수량 합계와 체크수량을 비교하여

 - 입력수량이 맞으면? 그대로 업데이트 
    : 네
 - 입력수량이 모자라면? 추가로 인서트
    : 네
 - 입력수량이 넘치면? 어떻게 하나요? 체크수량에 맞도록 업데이트?
    : 네 체크수량에 맞도록만 업데이트 입니다.

by lgxj20 [2024.02.20 19:21:32]

하루종일 생각중인데 이게 되면 저게 안되고 그러네요 ㅜㅜ 3가지 조건으로 다 된야되는 상황이라..

by 마농 [2024.02.21 08:42:40]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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
;

 

by lgxj20 [2024.02.23 14:41:40]

감사합니다.

이렇게 하면 되는군요...역시 많이 배우게 됩니다.^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입