Oracle - Check if row exists before deleting several rows in other tables

Question

I would like to delete several rows in an Oracle database if the ID of a main table already exists: I tried to do first a SELECT to check if the ID exists in the main table before deleting the rows based on the ID in the other tables:

DECLARE
    PROJECT_ID PLS_INTEGER;
    PROJECT_ID_TO_DELETE PLS_INTEGER;
BEGIN
    PROJECT_ID := PROJECTS_SEQ.nextval; 
    SELECT PROJ_ID INTO PROJECT_ID_TO_DELETE FROM PROJECTS WHERE PROJ_NAME_EN LIKE 'FD Project - CSB - A.1 - CFT - Final Reject of an Increase Movement - Seb';    
    IF PROJECT_ID_TO_DELETE IS NULL THEN
        DELETE FROM PROJECTS_ORG WHERE PROJ_ID = PROJECT_ID_TO_DELETE;
        DELETE FROM PROJECTS WHERE PROJ_ID  = PROJECT_ID_TO_DELETE;
        DELETE FROM MVT_ONGOING WHERE MVT_ONGOING_PROJ_ID  = PROJECT_ID_TO_DELETE;
        DELETE FROM MVT_HISTORY WHERE MVT_HISTORY_PROJ_ID  = PROJECT_ID_TO_DELETE;
    END IF;
    Insert into PROJECTS (PROJ_ID, ...) values (PROJECT_ID, ...);
    Insert into PROJECTS_ORG (PROJ_ID, ...) values (PROJECT_ID, ...);
    Insert into MVT_ONGOING (MVT_ONGOING_PROJ_ID, ...);
    Insert into MVT_HISTORY (PROJ_ID, ...) values (PROJECT_ID, ...);
END;

I get an error "no data found"

Could you please help me with that in order to find the best optimized solution?

Thanks

Answer 1

Something like this:

DECLARE
   project_id_new        PLS_INTEGER;
   project_id_to_delete  PLS_INTEGER;
BEGIN
   BEGIN
      SELECT proj_id
        INTO project_id_to_delete
        FROM projects
       WHERE proj_name_en LIKE 'blabla';

      DELETE FROM projects_org WHERE proj_id = project_id_to_delete;
      DELETE FROM projects     WHERE proj_id = project_id_to_delete;
      DELETE FROM mvt_ongoing  WHERE mvt_ongoing_proj_id = project_id_to_delete;
      DELETE FROM mvt_history  WHERE mvt_history_proj_id = project_id_to_delete;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
   END;

   project_id_new := seq.nextval;
   
   INSERT INTO projects (project_id)     VALUES (project_id_new);
   INSERT INTO projects_org (project_id) VALUES (project_id_new);
END;

What does it do?

• tries to find PROJ_ID
• if it finds it, performs DELETEs
  • you don't have to use IF; if it is found, it's known. If it isn't found, exception is raised
• otherwise, NO_DATA_FOUND exception is raised and handled
  • I chose to do nothing, you might want to do something else

As of inserting (as you commented, you'd want to do it always): enclose deleting section into its own BEGIN-EXCEPTION-END block so that possible no_data_found error is handled. Then, find project_id_new value (for example, using a sequence) and use it in INSERT statements.