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);
END;
/
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 ]
