Ahhh yes in 9i Merge required both update and insert clauses:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_915a.htm
However you can easily use update:
SQL> create table address (add_id number primary key, ward varchar2
(100));
Table created.
SQL> insert into address values (1, null);
1 row created.
SQL> insert into address values (2, 'something');
1 row created.
SQL> insert into address values (3, null);
1 row created.
SQL> insert into address values (4, 'something');
1 row created.
SQL> create table contacts (con_id number primary key, ward varchar2
(100));
Table created.
SQL> create table add_contacts (add_id number, con_id number);
Table created.
SQL> insert into add_contacts values (1, 1);
1 row created.
SQL> insert into add_contacts values (2, 2);
1 row created.
SQL> insert into contacts values (1, 'blahh');
1 row created.
SQL> insert into contacts values (2, 'blahhblahh');
1 row created.
SQL> commit;
Commit complete.
If youd like to update address.ward to whatever value contacts
contain, even if it is null, then just this update will be ok:
SQL> UPDATE address
2 SET ward = (
3 SELECT ward
4 FROM contacts, add_contacts
5 WHERE add_contacts.add_id = address.add_id
6 AND add_contacts.con_id = contacts.con_id)
7 /
4 rows updated.
Now it is as follows:
SQL> select * from address;
ADD_ID WARD
---------- ----------------
1 blahh
2 blahhblahh
3
4
OK let's rollback, now we have initial situation:
SQL> rollback;
Rollback complete.
SQL> select * from address;
ADD_ID WARD
---------- --------------------------
1
2 something
3
4 something
And now update to new values also keeping old values, if the new value
is null and old value was not null:
SQL> ed
Wrote file afiedt.buf
1 UPDATE address
2 SET ward = coalesce((
3 SELECT ward
4 FROM contacts, add_contacts
5 WHERE add_contacts.add_id = address.add_id
6* AND add_contacts.con_id = contacts.con_id), address.ward)
SQL> /
4 rows updated.
SQL> select * from address;
ADD_ID WARD
---------- ----------------------------------------------------------
1 blahh
2 blahhblahh
3
4 something
However looking at your query it seems there might be more than one
contact for each address, you should probably figure out which is the
relevant, otherwise you'll get an error.
Gints Plivna
http://www.gplivna.eu
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
