Oracle – How to Perform Update Join on Tables

oracle

I want to update my db records, which are related one to many. Please consider the following example :

A person can have many contacts

Stating above implies that contact table has a person_id column. And also adding to the context the contact table has current_year_id column. Table person has phone and fax columns.

Now I added phone and fax columns to the contact table and I want to copy the values of fax and column to the contact table from person table. However I want to set these values to the contact associated with person which has current_year_id from current_year table.

Here is what I tried so far, and am surprised why is not working :

UPDATE CONTACT SET PHONE = p.PHONE, FAX = p.FAX 
      FROM PERSON p
      INNER JOIN CONTACT on CONTACT.PERSON_ID = p.PERSON_ID
      WHERE CONTACT.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
      )

However select brings back the records :

SELECT * FROM PERSON p
INNER JOIN CONTACT on CONTACT.PERSON_ID = p.PERSON_ID
WHERE CONTACT.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
      );

Tried another variant :

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

Another one :

UPDATE CONTACT
        (SELECT PERSON.PHONE as PHONE, PERSON.FAX as FAX
         FROM PERSON
        ) p
        SET CONTACT.PHONE = p.PHONE,
        CONTACT.FAX = p.FAX
        WHERE CONTACT.CURRENT_YEAR_ID = (
                SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
              );

And get very informative error from sql developer :

SQL Error: ORA-00933: SQL command not properly ended

What can I do to achieve this?

Best Answer

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 ;
Related Question