ORACLE UPDATE using FULL OUTER JOIN and sub-query

Question

I have the following QUERY

SELECT DISTINCT EPS_PROPOSAL.PROPOSAL_NUMBER FROM PROP_ADMIN, EPS_PROPOSAL
    FULL OUTER JOIN PROP_ADMIN 
    ON EPS_PROPOSAL.PROPOSAL_NUMBER = PROP_ADMIN.PROPOSAL_NUMBER
    WHERE EPS_PROPOSAL.SPONSOR_CODE = 100728 AND 
        (EPS_PROPOSAL.STATUS_CODE = 3 OR EPS_PROPOSAL.STATUS_CODE = 6)

PROPOSAL_NUMBER    FUNDING_CODE
    4214              (null)
    3079              (null)
    3212              (null)
      .                  .
      .                  .
 TOTAL RECORDS: 339

I am attempting to update the FUNDING_CODE to F using the previously used WHERE condition and OUTER JOIN.

UPDATE PROP_ADMIN
    SET FUNDING_CODE = 'F'
    WHERE PROPOSAL_NUMBER IN(
       SELECT DISTINCT EPS_PROPOSAL.PROPOSAL_NUMBER FROM PROP_ADMIN, EPS_PROPOSAL
       FULL OUTER JOIN PROP_ADMIN 
       ON EPS_PROPOSAL.PROPOSAL_NUMBER = PROP_ADMIN.PROPOSAL_NUMBER
           WHERE EPS_PROPOSAL.SPONSOR_CODE = 100728 AND 
           (EPS_PROPOSAL.STATUS_CODE = 3 OR EPS_PROPOSAL.STATUS_CODE = 6)

When I run this, only 1 row is updated from my list above.

PROPOSAL_NUMBER     FUNDING_CODE
     4214                F
     3079              (null)
     3212              (null)
      .                  .
      .                  .

How do I make the UPDATE statement execute across all the rows instead of just the first row returned from the sub-query.

Answer 1

That is because only PROPOSAL_NUMBER 4214 exists in PROP_ADMIN table. All the remaining 338 PROPOSAL_NUMBER's would be from EPS_PROPOSAL table. Please note that you're performing a full outer join to pull those 339 records. So, it's quite obvious to see only a very few records being updated. Hope it helps!