title: MERGE INTO With between production and test where the tables to be merged need a join
body: <p>I'm writing a stored procedure that should sync a table from our production environment to our test environment (once a day). 
  I have 2 tables. To make it easy lets call them Meters and Measurements.</p>
  
  <pre><code>Meters = [Meter_id, Location]
  Measurements = [Meter_id, Value, MeasurementTime]
  </code></pre>
  
  <p>I want to keep Measurements in sync and lets assume that I can manually add a meter to my test environment. Meter_id can get out of sync between both databases but Location doesn't change. </p>
  
  <p>I have set up a database link to our test server called db_test. Now I would like to use a Merge statement to update Measurements@db_test</p>
  
  <pre><code>MERGE INTO Measurements@db_test meas_test
  USING Measurements meas
     ON (???)
  WHEN MATCHED THEN
     -- Do update
  WHEN NOT MATCHED THEN
     -- Do insert
  </code></pre>
  
  <p>I was thinking in this direction...</p>
  
  <pre><code>MERGE INTO Measurements@db_test meas_test
  USING (SELECT value, location 
         FROM meters mtr, measurements meas 
         WHERE mtr.meter_id = meas.meter_id AND 
         MeasurementTime &gt; sysdate - 1)  meas_new
  ON ( meas_new.location = ??? AND 
       meas_new.value = meas_test.value AND 
       meas_new.MeasurementTime = meas_test.MeasurementTime)            
  WHEN MATCHED THEN
     -- Do update
  WHEN NOT MATCHED THEN
     -- Do insert
  </code></pre>
  
  <p>So how do I lay the join to the Meters table so that I'm sure they match on location and not on ID</p>