Trying to delete from multiple tables using SQL

Question

I have 4 tables in our application:

• User
• usession
• upklist
• projshare

The last three tables contain a field called session_id.

In the code below, the section in parenthesis works to get all session_id values from usession table for user "awpeople".

The problem is how do I read this result set into an array and delete from all three tables where session_id is in the array results.

Code:

DELETE FROM usession, 
            upklist, 
            projshar 
WHERE  session_id = (SELECT session_id 
                     FROM   usession 
                     WHERE  delete_session_id IS NULL 
                            AND user_id = (SELECT user_id 
                                           FROM   users 
                                           WHERE  REGEXP_LIKE(USER_NAME, 
                                                  'awpeople', 'i'))); 

Solution 1

delete can only handle one table at a time, so you'd need three statements:

DELETE FROM upklist 
WHERE  session_id = (SELECT session_id 
                     FROM   usession 
                     WHERE  delete_session_id IS NULL 
                            AND user_id = (SELECT user_id 
                                           FROM   users 
                                           WHERE  REGEXP_LIKE(USER_NAME, 
                                                  'awpeople', 'i'))); 

DELETE FROM projshar 
WHERE  session_id = (SELECT session_id 
                     FROM   usession 
                     WHERE  delete_session_id IS NULL 
                            AND user_id = (SELECT user_id 
                                           FROM   users 
                                           WHERE  REGEXP_LIKE(USER_NAME, 
                                                  'awpeople', 'i'))); 

DELETE FROM usession 
WHERE  session_id = (SELECT session_id 
                     FROM   usession 
                     WHERE  delete_session_id IS NULL 
                            AND user_id = (SELECT user_id 
                                           FROM   users 
                                           WHERE  REGEXP_LIKE(USER_NAME, 
                                                  'awpeople', 'i'))); 

Note that since the inner query relies on usersession, you should delete from it last.

Solution 2

If usession has a unique or primary key on session_id, and the other tables have foreign key relationships to it, then you can just delete the row from usession and have the database cascade it to the child tables.