V20305J - SQL - ON DUPLICATE KEY - MERGE - 寫入資料庫時,若不存在,則 Insert ,若已存在,則Update - INSERT INTO -UPDATE

 目的: 寫入資料庫時,若不存在,則 Insert ,若已存在,則Update

         1> MySQL 語法 : ON DUPLICATE KEY UPDATE :  

               當插入的記錄的主鍵或唯一索引衝突時，執行 UPDATE 操作更新指定欄位

                MySQL語法 :   INSERT INTO table

                        (PK1,PK2,COL1,COL2)  

                        VALUES

                        (PK1_VAL,PK2_VAL,COL1_VAL,COL2_VAL) 

                                           ON DUPLICATE KEY UPDATE 

                                           COL1=COL1_VAL,

                                                COL2=VOL2_VAL

            2>Oracle  SQL語法 : 改用 MERGE 語法  -Oracle 不支援 ON DUPLICATE KEY UPDATE, 

MERGE INTO AMM_UOC target

USING (SELECT '768F0392571016' AS RPNOAF,

              'IDF' AS EIAC,

              'A' AS EI_LCN,

              '00' AS EI_ALC, 

              'A010L030600901' AS NHA_LCN,

              '00' AS NHA_ALC,

              'A010L03060090105' AS LCN,

              '00' AS ALC,

              '2122540-5' AS PN,

              'A201012300008' AS AMMNO,

              'N' AS EFF,

              'A64547' AS CAGE

        FROM dual) src

ON (   target.RPNOAF = src.RPNOAF 

    AND target.EIAC = src.EIAC 

    AND target.EI_LCN = src.EI_LCN

    AND target.EI_ALC = src.EI_ALC

    AND target.NHA_LCN = src.NHA_LCN

    AND target.NHA_ALC = src.NHA_ALC

    AND target.LCN = src.LCN

    AND target.ALC = src.ALC

    AND target.PN = src.PN

    )

WHEN MATCHED THEN

    UPDATE SET target.AMMNO = src.AMMNO,

               target.EFF = src.EFF,

               target.CAGE = src.CAGE

WHEN NOT MATCHED THEN

    INSERT (RPNOAF, EIAC, EI_LCN, EI_ALC, NHA_LCN, NHA_ALC, LCN, ALC, PN, AMMNO, EFF, CAGE)

    VALUES (src.RPNOAF, src.EIAC, src.EI_LCN, src.EI_ALC, src.NHA_LCN, src.NHA_ALC, src.LCN, src.ALC, src.PN, src.AMMNO, src.EFF, src.CAGE);

-->

MERGE INTO AMM_UOC B
USING (SELECT '768F0392571016' AS RPNOAF,
'IDF' AS EIAC,
'A' AS EI_LCN,
'00' AS EI_ALC,
'A010L030600901' AS NHA_LCN,
'00' AS NHA_ALC,
'A010L03060090105' AS LCN,
'00' AS ALC,
'2122540-5' AS PN,
'A201012300008' AS AMMNO,
'Y' AS EFF,
'A64547' AS CAGE
FROM dual) A
ON (        B.RPNOAF = A.RPNOAF
        AND B.EIAC = A.EIAC
        AND B.EI_LCN = A.EI_LCN
        AND B.EI_ALC = A.EI_ALC
        AND B.NHA_LCN = A.NHA_LCN
        AND B.NHA_ALC = A.NHA_ALC
        AND B.LCN = A.LCN
        AND B.ALC = A.ALC
        AND B.PN = A.PN
)
WHEN MATCHED THEN
              UPDATE SET B.AMMNO = A.AMMNO,
                                     B.EFF = A.EFF,
                                     B.CAGE = A.CAGE
WHEN NOT MATCHED THEN
          INSERT (RPNOAF, EIAC, EI_LCN, EI_ALC, NHA_LCN, NHA_ALC, LCN, ALC, PN, AMMNO, EFF, CAGE)
          VALUES 

(A.RPNOAF, A.EIAC, A.EI_LCN, A.EI_ALC, A.NHA_LCN, A.NHA_ALC, A.LCN, A.ALC, A.PN, A.AMMNO, A.EFF, A.CAGE);