Oracle 如何使用合并查询同步获取表

你好，汤姆，
我有2个表源和目标，我希望目标表在查询运行时与源同步。

以下是两个表的列结构。我希望除 (Contact_no) 之外的所有列都必须在目标表中同步。

CREATE TABLE T_SOURCE (ID NUMBER(5) , Part_no VARCHAR2(10), Country_Code VARCHAR2(2), Contact_no NUMBER(10));

INSERT INTO T_SOURCE VALUES (1, 'AB123', 'IN',2512121212);
INSERT INTO T_SOURCE VALUES (2, 'AD345', 'US',2518571122);
INSERT INTO T_SOURCE VALUES (3, 'RT123', 'CH',2512121212);
INSERT INTO T_SOURCE VALUES (4, 'UY123', 'EN',2587452548);

Source Table
================================================
Unique_ID Part_no    Country_Code   Contact_no
1         AB123      IN             2512121212
2         AD345      US             2518571122
3         RT123      CH             2512121212
4         UY123      EN             2587452548


CREATE TABLE T_TARGET (ID NUMBER(5) , Part_no VARCHAR2(10), Country_Code VARCHAR2(2), Action CHAR(1));

最初，我将使用Action = “i” 将3列数据复制到目标表中。

INSERT INTO T_TARGET SELECT ID,Part_no,Country_Code,'I' FROM T_SOURCE;

Target Table
===============================================
Unique_ID Part_no  Country_Code Action
1         AB123    IN           I
2         AD345    US           I 
3         RT123    CH           I
4         UY123    EN           I

现在，当SQL运行时，它将根据带有操作标志的源表更新，插入或删除目标表。

Next Day Source Table changed
================================================
Unique_ID Part_no  Country_Code    Contact_no
1         AB123    US              2512121212  -- Updated Country_Code as "US"
2         AD345    US              2518571122  -- No Change
3         RT123    CH              2512121212  -- Deleted from Source
4         UY123    EN              2587452548  -- No Change
5         APD25    DL              1111111111  -- New Inserted

Target Table
===============================================
Unique_ID Part_no  Country_Code Action
1  AB123           US    U
2  AD345           US    I 
3  RT123           CH    D -- Deleted from Source
4  UY123           EN    I
5  APD25           DL    I     -- Newly Inserted

我已经在下面写了Merge语句，但是它不能正常工作，在执行时使用操作标志 “I” 进行初始插入后，它正在更新所有使用操作标志为 “U” 的记录

MERGE INTO Target d2
USING (SELECT Unique_id, Part_no, Country_code 
       FROM Source
       UNION ALL
       SELECT a.Unique_id, a.Part_no, a.Country_code
       FROM Target a LEFT JOIN Source b ON a.Unique_id=b.Unique_id
       WHERE b.Unique_id IS NULL 
       ) d
ON (d2.Unique_id=d.Unique_id)
WHEN NOT MATCHED THEN
   INSERT(Unique_id, Part_no, Country_code, Action)
   VALUES(d.Unique_id, d.Part_no, d.Country_code, 'I')
WHEN MATCHED THEN
   UPDATE SET d2.Action = 'U', d2.Country_code = d.Country_code;
--   DELETE WHERE d2.loc='DELETE ME';

请帮我。

如果使用相同的源数据运行merge两次，则第一遍将加载任何丢失的行。在第二次执行时，源中的所有行都在目标中。所以你会更新所有的行!

为避免这种情况，请从目标中删除已存在负或不存在的行。

当您使用它时，要识别已删除的行，请将外部联接更改为从源返回列。

所以你的源查询会是这样的:

(
SELECT id, Part_no, Country_code 
FROM   T_SOURCE
MINUS
SELECT id, Part_no, Country_code 
FROM   T_TARGET
)
UNION ALL
SELECT a.id, b.Part_no, b.Country_code
FROM T_TARGET a 
LEFT JOIN T_SOURCE b 
ON a.id=b.id
WHERE b.id IS NULL

然后在MATCHED子句中，您可以检查source.part_no是否为null以删除或根据需要标记它:

CREATE TABLE T_SOURCE (
  ID NUMBER(5) , Part_no VARCHAR2(10), 
  Country_Code VARCHAR2(2), Contact_no NUMBER(10)
);

CREATE TABLE T_TARGET (
  ID NUMBER(5) , Part_no VARCHAR2(10), 
  Country_Code VARCHAR2(2), Action CHAR(1)
);

INSERT INTO T_SOURCE VALUES (1, 'AB123', 'IN',2512121212);
INSERT INTO T_SOURCE VALUES (2, 'AD345', 'US',2518571122);
INSERT INTO T_SOURCE VALUES (3, 'RT123', 'CH',2512121212);
INSERT INTO T_SOURCE VALUES (4, 'UY123', 'EN',2587452548);

INSERT INTO T_TARGET SELECT ID,Part_no,Country_Code,'I' FROM T_SOURCE;
commit;

delete t_source;
INSERT INTO T_SOURCE VALUES (1, 'AB123', 'US',2512121212);
INSERT INTO T_SOURCE VALUES (2, 'AD345', 'US',2518571122);
INSERT INTO T_SOURCE VALUES (4, 'UY123', 'EN',2587452548);
INSERT INTO T_SOURCE VALUES (5, 'UY123', 'EN',1111111111);
commit;

MERGE INTO T_TARGET d2
USING ((
        SELECT id, Part_no, Country_code 
        FROM   T_SOURCE
        MINUS
        SELECT id, Part_no, Country_code 
        FROM   T_TARGET
        )
       UNION ALL
       SELECT a.id, b.Part_no, b.Country_code
       FROM T_TARGET a 
       LEFT JOIN T_SOURCE b 
       ON a.id=b.id
       WHERE b.id IS NULL 
       ) d
ON (d2.id=d.id)
WHEN NOT MATCHED THEN
   INSERT(id, Part_no, Country_code, Action)
   VALUES(d.id, d.Part_no, d.Country_code, 'I')
WHEN MATCHED THEN
   UPDATE SET d2.Action = 'U', d2.Country_code = d.Country_code
   DELETE WHERE d.part_no is null;

select * from T_TARGET;

ID    PART_NO    COUNTRY_CODE    ACTION   
    1 AB123      US              U         
    2 AD345      US              I         
    4 UY123      EN              I         
    5 UY123      EN              I     

rollback;

MERGE INTO T_TARGET d2
USING ((
        SELECT id, Part_no, Country_code 
        FROM   T_SOURCE
        MINUS
        SELECT id, Part_no, Country_code 
        FROM   T_TARGET
        )
       UNION ALL
       SELECT a.id, b.Part_no, b.Country_code
       FROM T_TARGET a 
       LEFT JOIN T_SOURCE b 
       ON a.id=b.id
       WHERE b.id IS NULL 
       ) d
ON (d2.id=d.id)
WHEN NOT MATCHED THEN
   INSERT(id, Part_no, Country_code, Action)
   VALUES(d.id, d.Part_no, d.Country_code, 'I')
WHEN MATCHED THEN
   UPDATE SET 
     d2.Action = case 
       when d.part_no is not null then 'U' 
       else 'D' 
     end, 
     d2.Country_code = case 
       when d.part_no is not null then d.Country_code 
       else d2.Country_code 
    end;

select * from T_TARGET;

ID    PART_NO    COUNTRY_CODE    ACTION   
    1 AB123      US              U         
    2 AD345      US              I         
    3 RT123      CH              D         
    4 UY123      EN              I         
    5 UY123      EN              I