title: Hive - Update records in a table with todays date IF they are not found in another table?
body: <p>I currently have a Main result table (test1) that stores all of my records of issues and a second table (test2) that is run every week or so and I'm trying to find those records where not exists in the weekly update and then update the date in the main result table as that is when it got updated in the system to be corrected.</p>
  
  <p>I am trying to add the records from <code>test2</code> table to <code>test1</code> table if they are not already in the table.</p>
  
  <p>This works: </p>
  
  <pre><code>insert into table test1 (id, name, code)
  select * from test2 t2 where t2.id not in (select id from test1);
  </code></pre>
  
  <p>I'm trying to also update table <code>test1</code> <code>'Corrected_date'</code> column to show current_date for all records that are found in <code>test1</code> but not in <code>test2</code> </p>
  
  <p>example data below:</p>
  
  <p>Table 1</p>
  
  <pre><code>ID    NAME    CODE    CORRECTED_DATE
  1     TEST    3    
  29    TEST2   90 
  </code></pre>
  
  <p>Table 2</p>
  
  <pre><code>ID    NAME    CODE  
  12    TEST5   20
  1     TEST    3
  </code></pre>
  
  <p>Expected End Result of Table 1</p>
  
  <pre><code>ID    NAME    CODE    CORRECTED_DATE
  1     TEST    3       
  29    TEST2   90       3/13/2019
  12    TEST5   20
  </code></pre>