title: SYNC and UPDATE at the same time between two tables in Oracle SQL
body: <p>I am having trouble on ORACLE SQL Operation. 
  So first of all, I have two tables,</p>
  
  <pre><code>TEST_TABLE_A
  Insert into TEST_TABLE_A (NAME, VAL1, VAL2, STATUS) Values ('HEAD1', 100, 200, 'ACTIVE');
  Insert into TEST_TABLE_A (NAME, VAL1, VAL2, STATUS) Values ('HEAD2', 300, 400, 'ACTIVE');
  Insert into TEST_TABLE_A (NAME, VAL1, VAL2, STATUS) Values ('HEAD3', 500, 600, 'ACTIVE');
  Insert into TEST_TABLE_A (NAME, VAL1, VAL2, STATUS) Values ('HEAD4', 700, 800, 'ACTIVE');
  
  TEST_TABLE_B
  Insert into TEST_TABLE_B (NAME, VAL1, VAL2) Values ('HEAD1', 1, 2);
  Insert into TEST_TABLE_B (NAME, VAL1, VAL2) Values ('HEAD4', 700, 800);
  Insert into TEST_TABLE_B (NAME, VAL1, VAL2) Values ('HEAD5', 900, 1000);
  </code></pre>
  
  <p>My assignment is listed as follows,
  1. Sync data from table b to table a
  2. when the same name found with the same values then ignore
  3. when the same name found with different values then insert the data into table A with the same name and the value grabbed from B and update the old row  with 'INACTIVE' status
  4. when no data found in table A based on table B then insert the data into table A</p>
  
  <p>my query is like this,</p>
  
  <pre><code>    MERGE INTO TEST_TABLE_A TGT
      USING TEST_TABLE_B SRC
          ON (TGT.NAME = SRC.NAME)
  
      WHEN MATCHED THEN 
          UPDATE SET TGT.VAL1 = SRC.VAL1,
                 TGT.VAL2 = SRC.VAL2
  
      WHEN NOT MATCHED THEN
          INSERT (TGT.NAME, TGT.VAL1, TGT.VAL2, TGT.STATUS)
          VALUES (SRC.NAME, SRC.VAL1, SRC.VAL2, 'ACTIVE');
  </code></pre>
  
  <p>So I am facing the problem that i cant make #3 work. I tried many combination of SQL and its just doesnt happen to work. </p>
  
  <p>And how do i make number 2 work by ignoring the same name with same values in both table?</p>
  
  <p>I am hoping that the result would be like this</p>
  
  <pre><code>TEST_TABLE_A
  NAME   VAL1   VAL2   STATUS
  -----------------------------
  HEAD1  100    200    INACTIVE
  HEAD1  1      2      ACTIVE
  HEAD2  300    400    INACTIVE
  HEAD3  500    600    INACTIVE
  HEAD4  700    800    ACTIVE
  HEAD5  900    1000   ACTIVE
  </code></pre>
  
  <p>I really appreciate you guys help.
  regards</p>