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?