4 Replies Latest reply: Jan 2, 2013 12:32 PM by rp0428 RSS

    Delete records from child and parent table.

    user9077483
      Hi Experts,

      I want to delete records from parent table which are less than 2 years.
      Before deleting records from parent table we have to delete records from child table
      how can we delete those records.
      I don't want to use ON DELETE CASCADE.
      MASS_MASTER --parent table.
      MASS_CHILD --child table.
      The below query is used to delete records from parent table.
      DELETE FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24);
      The child table MASS_CHILD is not having last_date column.

      Please provide me the query to delete same records from child table.

      Please help me.

      Thanks in advance.
        • 1. Re: Delete records from child and parent table.
          908002
          assuming there is relation between these 2 and id of mass_master column references to mass_child... ( assuming the col name mass_master_id)
          DELETE FROM mass_child where mass_matster_id in(select id FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24));
          
          delete FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24);
          • 2. Re: Delete records from child and parent table.
            user9077483
            Thanks for your reply.

            I have written the query as below and it's working fine.
            DELETE FROM mass_child child where exists(select 1 FROM mass_master master
            WHERE child.m_id=master.m_id AND last_date<=ADD_MONTHS(sysdate,-24));
            However for my another table last_date column is not there in parent table and last_date column is existed in child table.
            And the business user requirement is delete records from parent table which are less than 3 months.
            It will be like below query.
            DELETE FROM GRAG_MASTER WHERE column_name<=ADD_MONTHS(sysdate,-3);
            Then how can we delete data from parent table.
            DESC GRAG_MASTER
            g_id,
            gname,
            gloc
            DESC GRAG_CHILD
            g_id,
            g_status,
            g_code,
            last_date
            Please help me.
            • 3. Re: Delete records from child and parent table.
              Peter vd Zwan
              Hi,

              The normal setup for master child relations is:
              master row in master table with related 0 to many child rows in child table.
              In you example we can have a master row with more child rows. Some of these child rows can be more and some less then 3 months old.

              So deleting master rows based on child rows criteria is not logic.

              What you can do is delete all child rows based on the date criteria and then delete all master rows without related child rows.

              Something like:
              delete GRAG_CHILD
              where
                last_date < add_months(sysdate,-3)
              ;
              
              delete GRAG_MASTER
              where
                not exists ( select * from GRAG_CHILD where g_id = GRAG_MASTER.g_id)
              ;
              Regads,

              Peter
              • 4. Re: Delete records from child and parent table.
                rp0428
                >
                However for my another table last_date column is not there in parent table and last_date column is existed in child table.
                And the business user requirement is delete records from parent table which are less than 3 months.
                >
                If the parent table doesn't have a 'last_date' column how do you determine that a row is less than 3 months old?