DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: update

  1. #1
    Join Date
    Jun 2002
    Posts
    15
    Hello

    can we update the records in two tables at a time?

    Like, we are able to select the records from two tables by means of join, how about with the case of update.

    Thank you

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    no
    Jeff Hunter

  3. #3
    Join Date
    Jun 2002
    Posts
    15
    Then, what is the other way how i can fix it?

    Thank you

  4. #4
    Join Date
    Jun 2002
    Posts
    10
    Let's say you want to update table 2 and table 3 at the same time table 1 is updated.
    You create a trigger "ON UPDATE" on table 1 and in that trigger you update table 2 and table 3.

  5. #5
    Join Date
    Dec 2000
    Posts
    126
    how about create a view joining 2 tables, and update this VIEW will update these 2 underlined tables

  6. #6
    Join Date
    Jun 2002
    Posts
    15
    could you please be more clear and tell me how should i proceed.can you give me the syntax.

    Thank you

  7. #7
    Join Date
    Feb 2001
    Posts
    180
    Example:

    create view YourView
    as
    select ...
    from YourTable1
    , YourTable2
    where (join-clause)

    create or replace trigger YourTrigger
    INSTEAD OF DELETE OR INSERT OR UPDATE
    ON YourView
    declare
    --
    begin
    if inserting
    then
    -- Insert statements
    insert into YourTable1
    ( column11)
    values
    (:new.viewcolumn)
    ;
    insert into YourTable2
    ( column21)
    values
    (:new.viewcolumn)
    ;
    elsif updating
    then
    update YourTable1
    set column11 = :new.viewcolumn1
    where column12 = :new.viewcolumn2
    ;
    update YourTable2
    set column21 = :new.viewcolumn1
    where column22 = :new.viewcolumn3
    ;
    elsif deleting
    then
    ...
    end if; -- inserting/updating/deleting

    end;



    Regards
    Ben de Boer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width