title: Compare 2 tables (TABLE_A vs TABLE_B) in Oracle 11g to find difference between them, Facing issue to do 1 to many mapping for INSERT in single query
body: <p>Compare 2 tables (TABLE_A vs TABLE_B)  in Oracle 11g  to find difference between them, Facing issue to do 1 to many mapping for INSERT in single query. Where same product has to be inserted multiple times in each Order where PRODUCT_TYPE_ID matches</p>
  <pre><code>CREATE TABLE &quot;TABLE_A&quot; 
     (    &quot;PRODUCT_TYPE_ID&quot; NUMBER(5,0), 
      &quot;PRODUCT_TYPE&quot; VARCHAR2(50 BYTE), 
      &quot;PRODUCT_ID&quot; NUMBER(10,0), 
      &quot;PRODUCT_NAME&quot; VARCHAR2(100 BYTE)
     )
  
  CREATE TABLE &quot;TABLE_B&quot; 
     (    &quot;ORDER_ID&quot; NUMBER(10,0), 
      &quot;PRODUCT_TYPE_ID&quot; NUMBER(5,0), 
      &quot;PRODUCT_TYPE&quot; VARCHAR2(50 BYTE), 
      &quot;PRODUCT_ID&quot; NUMBER(10,0), 
      &quot;PRODUCT_NAME&quot; VARCHAR2(100 BYTE)
     )   
  
  
  
  Insert into TABLE_A (PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (8,'HardCopy',4101,'Paths of glory');
  Insert into TABLE_A (PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (8,'HardCopy',4102,'Atlas shrugged - Ayn Rand');
  Insert into TABLE_A (PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (9,'Electronic',4201,'NY times');
  Insert into TABLE_A (PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (9,'HardCopy',4202,'Wallstreet Journal');
  Insert into TABLE_A (PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (8,'Electronic',4103,'War and Peace - Leo Tolstoy');
  
  
  Insert into TABLE_B (ORDER_ID,PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (100,8,'Electronic',4107,'Zen Begineers mind');
  Insert into TABLE_B (ORDER_ID,PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (100,8,'HardCopy',4101,'paths of glory');
  Insert into TABLE_B (ORDER_ID,PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (100,8,'HardCopy',4102,'Atlas shrugged - Ayn Rand');
  Insert into TABLE_B (ORDER_ID,PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (100,9,'HardCopy',4211,'Forbes');
  Insert into TABLE_B (ORDER_ID,PRODUCT_TYPE_ID,PRODUCT_TYPE,PRODUCT_ID,PRODUCT_NAME) values (101,8,'Electronic',4111,'The Adventure of Hukleberry Finn ');
  
  
  SELECT 
  CASE
      WHEN t1.PRODUCT_ID IS NULL THEN 'DELETE'
      WHEN t2.PRODUCT_ID IS NULL then 'INSERT'
      WHEN t1.PRODUCT_ID is NOT NULL and t2.PRODUCT_ID IS NOT NULL then 'UPDATE'     
  END AS STATUS,
  CASE
      WHEN t2.PRODUCT_ID IS NULL and t1.PRODUCT_ID is NOT NULL then t2.Order_id
      ELSE t2.Order_id
  END AS Order_id,
  CASE
      WHEN t1.PRODUCT_TYPE_ID IS NULL THEN t2.PRODUCT_TYPE_ID
      ELSE t1.PRODUCT_TYPE_ID
  END AS PRODUCT_TYPE_ID ,
  CASE
      WHEN t1.PRODUCT_TYPE IS NULL THEN t2.PRODUCT_TYPE
      ELSE t1.PRODUCT_TYPE
  END AS PRODUCT_TYPE, 
  CASE
      WHEN t1.PRODUCT_ID IS NULL THEN t2.PRODUCT_ID
      ELSE t1.PRODUCT_ID
  END AS PRODUCT_ID,
  CASE
      WHEN t1.PRODUCT_NAME IS  NULL THEN t2.PRODUCT_NAME
      ELSE t1.PRODUCT_NAME
  END AS PRODUCT_NAME
  FROM table_A t1
  FULL JOIN table_B t2 on t1.PRODUCT_ID = t2.PRODUCT_ID
  WHERE 1 in (decode(t1.PRODUCT_TYPE_ID,t2.PRODUCT_TYPE_ID,0,1), decode(t1.PRODUCT_TYPE,t2.PRODUCT_TYPE,0,1),
  decode(t1.PRODUCT_ID,t2.PRODUCT_ID,0,1),decode(t1.PRODUCT_NAME,t2.PRODUCT_NAME,0,1));
  </code></pre>
  <p>Expected Output -</p>
  <pre><code>STATUS  ORderId Product_type_id Product_type    Product_Id     Product_name
  ------------------------------------------------------------------------------
  DELETE  100     8               Electronic      4107           Zen Begineers mind
  UPDATE  100     8               HardCopy         4101          Paths of glory
  DELETE  100     9               HardCopy        4211           Forbes
  INSERT  100     9               Electronic      4201           NY times
  INSERT  100     9               HardCopy        4202           Wallstreet Journal
  
  DELETE  101     8               Electronic      4111           The Adventure of Hukleberry Finn 
  INSERT  101     8               Electronic      4103           War and Peace - Leo Tolstoy        
  INSERT  101     8               HardCopy        4101           Paths of glory
  INSERT  101     8               HardCopy        4102           Atlas shrugged - Ayn Rand
  </code></pre>
  <p>TABLE_A is product catalogue that gets refreshed End of day
  Row with PRODUCT_ID can be modified, data in any column against it can be updated ( e.g change in Product_name),
  a new product_id could be added or existing product_id could be deleted.
  All product_id belong to certain type ie. Product_type_ID, so if there are 2 entries in TABLE_A now, it can increase or shrink tomorrow (never 0)
  Modification in TABLE_A is not in our control</p>
  <p>TABLE_B is Orders data. At day end, need to sync product info in TABLE_B with latest data from TABLE_A.
  We can identify relation, based on table_A.product_id = table_B.product_id.
  One Order_ID can have multiple PRODUCT_TYPE_IDs and that PRODUCT_TYPE_ID in turn can have multiple PRODUCT_IDs.</p>
  <p>Eg
  In TABLE_B, Order_id = 101  has PRODUCT_TYPE_ID=8. On comparison table_A.product_id = table_B.product_id existing record in TABLE_B has to be marked DELETE, as it's not in TABLE_A
  As Order_id = 101 has PRODUCT_TYPE_ID =8, we've to look up TABLE_A with PRODUCT_TYPE_ID=8 and bring in latest data into TABLE_B thus making 3 INSERTS (3 rows in TABLE_A for PRODUCT_TYPE_ID=8)
  this has to be done for all Order_IDs with PRODUCT_TYPE_ID =8, like Order_id=100
  Thus 1 set of records from TABLE_A needs to be INSERTed multiple times in every Order_ID where PRODUCT_TYPE_ID matches</p>
  <p>Use PRODUCT_ID to join and do comparison what matches and what doesn't --&gt; I've used it to evaluate DELETE and UPDATE (above query)
  Use PRODUCT_TYPE_ID to find out which all Order_IDs needs INSERT ---&gt; struggling to do  1 to many mapping</p>