This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
You either do multiple update statments (and there is nothing difficult with that) or you have to do it in a trigger.
CREATE OR REPLACE VIEW vw_for_update AS SELECT s.student_name ,c.class_name ,s.student_id ,c.class_id FROM student s JOIN class c USING (class_id); /
CREATE OR REPLACE TRIGGER tr_on_vw_for_update INSTEAD OF UPDATE ON vw_for_update BEGIN UPDATE student SET student_name = :NEW.student_name WHERE student_id = :OLD.student_id AND class_id = :OLD.class_id;
UPDATE class SET class_name = :NEW.class_name WHERE class_id = :OLD.class_id AND EXISTS (SELECT 1 FROM student WHERE student_id = :OLD.student_id AND class_id = :OLD.class_id);
then you would peform your update
UPDATE vw_for_update SET student_name = 'Jeneive', class_name='OAS' WHERE student_id = 1234 AND class_id = 5678; /
I would suggest... based on your question... that you should consider the easiest and simplest option (which is usually logically the best)... the multiple update statement route. [ November 01, 2007: Message edited by: Paul Campbell ]