MERGE INTO With between production and test where the tables to be merged need a join

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.

Meters = [Meter_id, Location]
Measurements = [Meter_id, Value, MeasurementTime]

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.

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

MERGE INTO Measurements@db_test meas_test
USING Measurements meas
   ON (???)
WHEN MATCHED THEN
   -- Do update
WH开发者_如何学CEN NOT MATCHED THEN
   -- Do insert

I was thinking in this direction...

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 > 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

So how do I lay the join to the Meters table so that I'm sure they match on location and not on ID

Can't you just join to the test environment's Meters table over the link? Like this:

MERGE INTO measurements@db_test meas_test
USING (SELECT mtr_test.meter_id, meas.value, meas.measurementtime
         FROM meters mtr, measurements meas, meters@db_test mtr_test
        WHERE mtr.meter_id = meas.meter_id
          AND mtr_test.location = mtr.location
          AND meas.measurementtime > sysdate - 1) meas_new
   ON (meas_new.meter_id = meas_test.meter_id
       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