Multiple Update

[Jeneive Dhanapalan]

Is there any way to update Multiple Tables , not a single table update

[Paul Campbell]

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 ]