How to delete multiple rows from multiple tables using Where clause?

Question

Using an Oracle DB, I need to select all the IDs from a table where a condition exists, then delete the rows from multiple tables where that ID exists. The pseudocode would be something like:

SELECT ID FROM TABLE1 WHERE AGE > ?
DELETE FROM TABLE1 WHERE ID = <all IDs received from SELECT>
DELETE FROM TABLE2 WHERE ID = <all IDs received from SELECT>
DELETE FROM TABLE3 WHERE ID = <all IDs received from SELECT>

What is the best and most efficient way to do this?

I was thinking something like the following, but wanted to know if there was a better way.

PreparedStatement selectStmt = conn.prepareStatment("SELECT ID FROM TABLE1 WHERE AGE > ?");
selectStmt.setInt(1, age);
ResultSet rs = selectStmt.executeQuery():

PreparedStatement delStmt1 = conn.prepareStatment("DELETE FROM TABLE1 WHERE ID = ?");
PreparedStatement delStmt2 = conn.prepareStatment("DELETE FROM TABLE2 WHERE ID = ?");
PreparedStatement delStmt3 = conn.prepareStatment("DELETE FROM TABLE3 WHERE ID = ?");

while(rs.next())
{
    String id = rs.getString("ID");

    delStmt1.setString(1, id);
    delStmt1.addBatch();

    delStmt2.setString(1, id);
    delStmt2.addBatch();

    delStmt3.setString(1, id);
    delStmt3.addBatch();
}

delStmt1.executeBatch();
delStmt2.executeBatch();
delStmt3.executeBatch();

Is there a better/more efficient way?

How would I use a WHERE IN with a variable amount of IDs, seem weird and silly to like find the number of rows in the ResultSet and create a string of question marks based off that. — MiketheCalamity, Aug 31 '15 at 16:46
It's more silly to send a command to the database multiple times inside a for loop instead of sending it once amd highly improving performance. If performance (efficiency) is not your goal, then do it like this. — Luiggi Mendoza, Aug 31 '15 at 16:47
Seriously, [stop using tags in the title of the question](http://meta.stackexchange.com/q/19190/182862). — Luiggi Mendoza, Aug 31 '15 at 16:55
Its not a tag, its an important part of my question. I'm asking how to do this particular thing in Java. You keep making my question look like I'm trying to run a simple SQL command. — MiketheCalamity, Aug 31 '15 at 16:57
And to respond to your "solution". You're honestly suggesting I have a double loop to create my SQL statement? `while(n < rs.count){ sql += "?," }` then `while(rs.next()){ stmt.setString(n++, rs.getString("ID")) }` That seems super inefficient dependent on the number of rows. There has got to be a better way. — MiketheCalamity, Aug 31 '15 at 17:05
*Its not a tag, its an important part of my question. I'm asking how to do this particular thing in Java*. Again, this is already stated in the tags. There's no need to add the tag in the title. And, in the end, you're just executing plain sql statements... — Luiggi Mendoza, Aug 31 '15 at 17:07

score 2 · Accepted Answer · answered Aug 31 '15 at 18:26

You could do it with one DELETE statement if two of your 3 tables (for example "table2" and "table3") are child tables of the parent table (for example "table1") that have a "ON DELETE CASCADE" option.

This means that the two child tables have a column (example column "id" of "table2" and "table3") that has a foreign key constraint with "ON DELETE CASCADE" option that references the primary key column of the parent table (example column "id" of "table1"). This way only deleting from the parent table would automatically delete associated rows in the child tables.

Check out this in more detail : http://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php

score 1 · Answer 2 · answered Aug 31 '15 at 22:02

If you delete only few records of a large tables ensure that an index on the column ID is defined.

To delete the records from the table TABLE2 and 3 the best strategy is to use the CASCADE DELETE as proposed by @ivanzg - if this is not possible, see below.

To delete from TABLE1 a far superior option that a batch delete on a row basis, use signle delete using the age based predicate:

 PreparedStatement stmt = con.prepareStatement("DELETE FROM TABLE1 WHERE age > ?")
 stmt.setInt(1,60)
 Integer rowCount = stmt.executeUpdate()

If you can't cascade delete, use for the table2 and 3 the same concept as above but with the following statment:

 DELETE FROM TABLE2/*or 3*/ WHERE ID in (SELECT ID FROM TABLE1 WHERE age > ?)

General best practice - minimum logic in client, whole logic in the database server. The database should be able to do reasonable execution plan - see the index note above.

score 0 · Answer 3 · edited May 23 '17 at 10:26

DELETE statement operates a table per statement. However the main implementations support triggers or other mechanisms that perform subordinate modifications. For example Oracle's CREATE TRIGGER.

However developers might end up figuring out what is the database doing behind their backs. (When/Why to use Cascading in SQL Server?)

Alternatively, if you need to use an intermediate result in your delete statements. You might use a temporal table in your batch (as proposed here).

As a side note, I see not transaction control (setAutoCommit(false) ... commit() in your example code. I guess that might be for the sake of simplicity.

Also you are executing 3 different delete batches (one for each table) instead of one. That might negate the benefit of using PreparedStatement.

How to delete multiple rows from multiple tables using Where clause?

3 Answers3