• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Multiple Update

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is there any way to update Multiple Tables , not a single table update
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic