Table update based on match/nomatch

Question

I have two tables t1 and t2.

t1 has this structure:

yearmonth
account
company
RCV_amount_t1

t2 has this structure:

yearmonth
account 
company
billing amount
billing amount CM_1
billing amount CM_2
RCV_amount_t2

I want to join t2 to t1 using yearmonth, account, and company. If they match, I want to update RCV_amount_t2 with the value in RCG_amount_t1. Otherwise, I want to set RCV_amount_t2 to spaces.

In the same manner, I want to join t1 with t2 using yearmonth, account, and company and set values accordingly.

Is it possible to achieve? If so, how do I go about it?

Answer 1

You'll want to use a MERGE.
It allows you to join two tables and specify how to update the values if they match.

The general structure of a MERGE statement looks like:

MERGE INTO driver_table
USING other_table
ON
(
    driver_table.column1 = other_table.column1
AND driver_table.column2 = other_table.column2
AND ...
)
WHEN MATCHED THEN UPDATE
    SET
        driver_table.some_column = other_table.some_value,
        driver_table.some_flag = 'Y',
        ...
;

Answer 2

I want to join t2 to t1 using yearmonth, account, and company. If they match, I want to update RCV_amount_t2 with the value in RCG_amount_t1. Otherwise, I want to set RCV_amount_t2 to spaces.

This will update the matching rows with the appropriate value, and update the rows with no match to NULL. If the field is numeric, you can't update it to "spaces"; NULL would be the appropriate indicator of no value. If the field is not numeric, then you could do a second update to replace NULL values with whatever you like, but NULL would still seem to me to be the most appropriate indicator of no value.

UPDATE t2 SET rcv_amount_t2 = (
  SELECT rcv_amount_t1
    FROM t1
    WHERE t1.yearmonth = t2.yearmonth
    AND t1.account = t2.account
    AND t1.company = t2.company
  )

Answer 3

It seems that we cannot resolve it in a single query, we need a merge and a correlated query, It works fine for me:

This will update t2 with values from t1 when matched:

MERGE INTO t2
   USING (SELECT yearmonth, account, company, RCV_amount_t1 FROM t1) S
   ON (t1.yearmonth = t2.yearmonth and
       t1.account = t2.account and
       t1.company = t2.company)
   WHEN MATCHED THEN UPDATE SET t2.RCV_amount_t2 = S.RCV_amount_t1;

Then a query containg a corrolated subquery to blank when not matched:

update t2 set RCV_amount_t2 = '    ' where yearmonth||account||company not in(
select yearmonth||account||company from t1 
where t1.yearmonth = t2.yearmonth and t1.account=t2.account and t1.company=t2.company);