CODEWITHSUNDEEP
sqloracle-databaseplsql
David Silva
David Silva

Reputation: 2017

Removing records from related tables

I have two tables:

which are related by foreign key content_id on reports table.

I need to create procedure which delete some reports together with their contents, like this:

DELETE FROM report_contents WHERE id IN 
     (SELECT content_id FROM reports WHERE extra_value = extraValue)

DELETE FROM reports WHERE extra_value = extraValue

But it is impossible to delete records from report_contents table firstly, because there is constrain on content_id column on reports table.

On the other hand when I delete records from reports table firstly, I won't know what report_contents should be deleted then...

CREATE OR REPLACE PROCEDURE delete_reports (extraValue NUMBER) IS
BEGIN
/* removing reports with extra_value = extraValue */
/* removing their report_contents */
END;

What is the best way to do this? (I don't want to add on delete cascade constrain)

Upvotes: 0

Views: 62

Answers (3)

Richard Pascual
Richard Pascual

Reputation: 2021

EDIT: Thanks to a helpful commenter, who pointed out how my original solution left out a relationship betweenCONTENT_ID of the REPORTS table and ID of REPORT_CONTENT table. The cursor query of my first attempt assumed that any orphaned ID in the REPORT_CONTENT table would be an ideal candidate for deletion. This assumption is not supportable so I rewrote the cursor into two different cursors below.

I think the original post was a question about how to do this in ORACLE`? Here's an alternate approach using an Oracle PL/SQL CURSOR. 

 CREATE or REPLACE PROCEDURE proc_delete_reports ( p_extra_value in varchar2 )
    is

    CURSOR del_reports is
       SELECT content_id FROM reports WHERE extra_value = p_extra_value
          FOR UPDATE;

    CURSOR del_contents (p_content_id IN number) is
       SELECT id
         FROM report_contents
        WHERE id = p_content_id 
          FOR UPDATE;

 BEGIN
    FOR i in del_reports
      LOOP

      DELETE FROM reports
       WHERE current of del_reports;

      FOR j in del_contents(p_content_id => i.content_id)
        LOOP

        DELETE from report_contents
        WHERE current of del_contents;
      END LOOP;

    END LOOP;
    COMMIT;

 END proc_delete_reports;

Given the appropriate syntax, you can modify the contents of a cursor output as you walk through each value in a loop.

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

If the number of ids is relatively small (i.e. just a few hundred or thousand) you can comfortably store the IDs to delete temporarily in a PL/SQL array.

PROCEDURE delete_reports (extraValue NUMBER) IS
  TYPE id_table IS TABLE OF reports.content_id%TYPE INDEX BY BINARY_INTEGER;
  ids id_table;

BEGIN

  /* which reports to delete? */
  SELECT content_id BULK COLLECT INTO ids
  FROM reports WHERE extra_value = p_extra_value;

  /* removing reports with extra_value = extraValue */
  DELETE reports WHERE extra_value = p_extra_value;

  /* removing their report_contents */
  FORALL i IN 1..ids.COUNT
    DELETE report_contents WHERE id = ids(i);

END delete_reports;

If the number of ids is large (e.g. millions or more) then I'd probably break this into a loop and get the ids in batches.

Upvotes: 1

SoulTrain
SoulTrain

Reputation: 1904

Since its an SP, you could use an intermediate TABLE variable to store your results

CREATE OR REPLACE PROCEDURE delete_reports (extraValue NUMBER) IS
BEGIN
    DECLARE @TABLE table
    ( CONTENT_ID int)

    INSERT INTo @TABLE
    SELECT content_id FROM reports WHERE extra_value = extraValue

    DELETE FROM reports B WHERE EXISTS (SELECT * FROM @TABLE A WHERE A.Content_id=B.Content_id)

    DELETE FROM report_contents C WHERE EXISTS (SELECT * FROM @TABLE A WHERE A.Content_id=C.ID)
END

I am assuming that you could use CONTENT_ID to delete from both tabes

Upvotes: 0

Related Questions