aspose file tools*
The moose likes Oracle/OAS and the fly likes Multiple Update Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Multiple Update" Watch "Multiple Update" New topic
Author

Multiple Update

Jeneive Dhanapalan
Greenhorn

Joined: Oct 18, 2007
Posts: 4
Is there any way to update Multiple Tables , not a single table update
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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 ]
 
 
subject: Multiple Update