I am trying to populate a table in Oracle 9i using corresponding data
from another table. The two tables have a many-to-many relationship -
they are related by a third table which holds primary keys from each
and their correspondence.
I would like to populate the destination table (address) using the
appropriate field (ward) from the source (contacts) for only the
records that match. I've tried various combinations of MERGE and
UPDATE, both with sub-clauses using SELECT, but with no luck.
For example
MERGE INTO address
USING ( SELECT add_id, con_id, ward
FROM address, contacts, add_contacts
WHERE address.add_id = add_contacts.add_id (+) AND
add_contacts.con_id = contacts.con_id (+) ) tmpadd
ON address.add_id = tmpadd.add_id
WHEN MATCHED THEN UPDATE
SET address.ward = tmpadd.ward;
but even with this I get 'ORA-00905: missing keyword'. Am I missing
something obvious?
The tables are set up like this roughly:-
address:
---------------------------------
add_id | postcode | ward |
---------------------------------
1 | 12323 | |
2 | 93543 | |
3 | 87141 | |
contacts:
---------------------------------
con_id | surname | ward |
---------------------------------
1 | thomas | ward1 |
2 | richards | ward2 |
3 | henry | ward1 |
add_contacts:
------------------
add_id | con_id |
------------------
1 |2 |
2 |3 |
and so on...
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
