Update a field on Table 1 based on criteria on Table 2? ORACLE

Question

I'm struggling to make a update script that works with the following data:

TOWER_PAYMENT TABLE                                         
============================================================== 
PAY_PERIOD|TOWER_ID|APARTMENT_NUM|MONTH_PAYMENT|PAYMENT_STATUS     
201703    |10      |101          |700          |NULL
201704    |10      |101          |700          |NULL
201703    |10      |102          |700          |NULL
201704    |10      |102          |700          |NULL                  
201703    |20      |101          |800          |NULL     
201704    |20      |101          |800          |NULL               
201703    |20      |102          |800          |NULL     
201704    |20      |102          |800          |NULL              

PAYMENT_STATUS TABLE
==============================================
PAY_PERIOD|TOWER_ID|APARTMENT_NUM|PAYMENT_DONE
201703    |10      |101          |700 
201704    |10      |101          |400 
201703    |10      |102          |500
201704    |10      |102          |700 
201703    |20      |101          |800 

I'm looking to update the "PAYMENT_STATUS" field with a number based on different criteria:

• If an "APARTMENT_NUM" belonging to a "TOWER_ID" is found in the "PAYMENT_STATUS" table for matching "PAY_PERIOD", then update the "PAYMENT_STATUS" field to 1.
• If the "APARTMENT_NUM" belonging to a "TOWER_ID is not found in the "PAYMENT_STATUS" table for matching "PAY_PERIOD", then update the "PAYMENT_STATUS" field to 2.
• If the "APARTMENT_NUM" have a "PAYMENT_DONE" lesser than "MONTH_PAYMENT", then update the "PAYMENT_STATUS" field to 3.

The desired output should be:

TOWER_PAYMENT
====================================================================     
PAY_PERIOD|TOWER_ID|APARTMENT_NUM|MONTH_PAYMENT|PAYMENT_STATUS     
201703    |10      |101          |700          |1
201704    |10      |101          |700          |3
201703    |10      |102          |700          |3
201704    |10      |102          |700          |1                  
201703    |20      |101          |800          |1     
201704    |20      |101          |800          |2               
201703    |20      |102          |800          |2     
201704    |20      |102          |800          |2            

I've tried with MERGE INTO but couldn't update when not matching.

Thanks in advance!

Answer 1

UPDATE tower_payment
   SET payment_status   = CASE
                              WHEN (SELECT payment_done
                                      FROM payment_status
                                     WHERE tower_payment.apartment_num = payment_status.apartment_num) < month_payment
                              THEN
                                  3
                              WHEN (SELECT payment_done
                                      FROM payment_status
                                     WHERE tower_payment.apartment_num = payment_status.apartment_num)
                                       IS NULL
                              THEN
                                  2
                              ELSE
                                  1
                          END;