Re: How to update 2 joinable tables in one satement ?

In principle you cannot do this other than through an 'instead of' trigger on a view as suggested in another post., and even then your example may require you to use deferrable constraints if a PK/FK relationship has been set up between the columns you are updating.

When updating a join, any table updated
has to be key-preserved - which means
any single row in the table is logically guaranteed to turn into a single row in the view. In your example, this is true of EMP (assuming we are joining on deptno, and
the PKhas been defined) but not of DEPT.

There is an undocumented hint though
/*+ bypass_ujvc */ which bypasses this
constraint. Any system using it will
probably be considered unsupported,
even though Oracle uses it internally
for some data dictionary manipulation.

(The PK/FK problem will probably still
apply though).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



R Chin wrote in message ...


>How can I update

>the joined records of

>EMP.colx and DEPT.coly

>in one statement ? Is this possible ?

>

>Thanks

>

>Robert

>-- 8.1.7/AIX --

>

>