Oracle – How to Match Regular Expressions

The error points to a syntax problem. When you want to update multiple columns at once, you list all of the columns to be updated first, followed by all of the values to use (in the same order that the columns are listed). For example:

UPDATE CONTACT C
SET (C.PHONE, C.FAX) = (
    SELECT P.PHONE, P.FAX
    FROM PERSON P
    WHERE P.PERSON_ID = C.PERSON_ID
)
WHERE C.CURRENT_YEAR_ID = (
    SELECT CURRENT_YEAR_ID
    FROM CURRENT_YEAR
    WHERE DEFAULT_YEAR = 1
);

(You'll have to check my logic, because I'm not entirely clear on your relationships.)

Or, instead of doing an update with a correlated sub-query (as above), you can write it as a merge:

MERGE INTO CONTACT C
USING (
    SELECT PERSON_ID, PHONE, FAX
    FROM PERSON
) P
    ON (P.PERSON_ID = C.PERSON_ID)
WHEN MATCHED THEN UPDATE
    SET C.PHONE = P.PHONE, C.FAX = P.FAX
WHERE C.CURRENT_YEAR_ID = (
    SELECT CURRENT_YEAR_ID
    FROM CURRENT_YEAR
    WHERE DEFAULT_YEAR = 1
);

And another way:

UPDATE 
    ( SELECT c.PHONE, c.FAX, 
             p.PHONE AS P_PHONE, p.FAX AS P_FAX
      FROM PERSON p
        INNER JOIN CONTACT c ON c.PERSON_ID = p.PERSON_ID
      WHERE c.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
        )
    ) x
SET 
    PHONE = P_PHONE, FAX = P_FAX ;

Solved this with help from https://stackoverflow.com/questions/3664903/how-can-i-return-multiple-identical-rows-based-on-a-quantity-field-in-the-row-it

The resulting answer with the original table looks like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
, results as (
  select n qty
  from qtys x
  join (
    select rownum n
    from dual
    connect by level <= (
      select max(qty)
      from qtys
    )
  ) y on y.n <= x.qty
)
select max(qty) qty
from (
  select count(1)
  , qty
  from results
  group by qty
  having count(1) = (
    select count(1)
    from qtys
  )
);

I'm certain I can condense it a little, but it produces the desired result.

EDIT

I suppose looking closer at this taking the minimum would have produced the same, desired result:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
select min(qty) qty
from qtys;