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')));

Could you clarify what you are trying to achieve? Your mention of reading the results into an array sounds like an attempted solution rather than the actual problem. If you need to delete from 3 tables, you just need to run 3 delete statements. — IMSoP
– IMSoP, Commented Jun 6, 2014 at 20:02
You can only delete from a single table at any given time - there's on way to delete from multiple tables at once — marc_s
– marc_s, Commented Jun 6, 2014 at 20:20

Mureinik · Accepted Answer · 2014-06-06 20:39:35Z

6

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.

answered Jun 6, 2014 at 20:39

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

diadyne Over a year ago

I could be wrong, but I believe that you can delete from multiple tables at the same time. See also: docs.oracle.com/cd/E17952_01/refman-5.1-en/delete.html

Mureinik Over a year ago

@diadyne That page is part of the Oracle MySQL documentation. Oracle database's delete, which OP asks about, does not support multiple table expressions.

David Aldridge · Accepted Answer · 2016-10-12 13:10:36Z

1

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.

answered Oct 12, 2016 at 13:10

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

2 Comments

Ryan Pelletier Over a year ago

I have done this in MySQL, do you know if it will work the same way in Oracle?

David Aldridge Over a year ago

Yes: it's pretty much a standard RDBMS feature to cascade deletes on a foreign key

qyb2zm302 · Accepted Answer · 2020-12-09 19:36:15Z

-3

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.] [, tbl_name[.]] ... FROM table_references [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.] [, tbl_name[.]] ... USING table_references [WHERE where_condition]

This comes from Oracle's MySQL 5.1 DELETE documentation.

edited Dec 9, 2020 at 19:36

qyb2zm302

6,4592 gold badges20 silver badges17 bronze badges

answered Feb 12, 2015 at 23:03

diadyne

4,13838 silver badges28 bronze badges

1 Comment

David Balažic Over a year ago

The link (it leads to error 404 now) appear to be for MySQL, while the question is about Oracle DB.

Collectives™ on Stack Overflow

Trying to delete from multiple tables using SQL

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related