1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To alter master and child table simultaniosly

Discussion in 'SQL PL/SQL' started by laxman, Aug 26, 2009.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear sir,
    Kindly help me to resolve this problem
    Consider the following table

    NOTE:Table ITEM is Master table and Table order_item is child table

    Table ITEM
    item_id unitprice
    0 0
    200 400
    201 1000
    202 100
    203 600
    10 500


    Table order_item
    Order_id item_id qty
    101 201 5
    101 202 4
    101 203 6
    102 204 7
    102 201 1
    102 10 3

    Now how we can delete item_id 10 from the ITEM table and make the corresponding field null in the ORDER_ITEM table.is it possible?

    Please suggest me
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    simple you will have to write an update statement after your delete statement!
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Yes.. In the same process to make the transaction synchronous:)

    Another way is to use the trigger . But it is not recommended unless there is no better way of doing it. I prefer Tyro's suggestion.
     
  4. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Sir This is with reference to your reply, if i am trying to delete the master table ,oracle error is coming such as child record found.

    My requirement is i want to delete the master table item for say item_id=10 then i want to update the child table with qty=null for item_id=10.

    Kindly help me with query.

    Thanks and regards
    Laxman
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Why do you want to sustain the record with item id=10 when the Foreign Constraints Contraint remains?

    Better you change the flag in Master instead of deleting and change the qty to NULL. If you want to make item_id as NULL you can use ON DELETE SET NULL