Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multiple Update

 
Jeneive Dhanapalan
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there any way to update Multiple Tables , not a single table update
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic