Delete from two tables in one statement

Question

I want to be able to remove all data from 2 tables where the id of a user = the id given. I am using Java, Derby DB, Netbeans 7.3 Beta 2 and OS X Mountain Lion.

I have two tables (sorry about the huge image):

enter image description here

This is my statement so far:

String stmt2 = "DELETE FROM APP.PERSON JOIN APP.DATAVAULT WHERE PID = ?";                    
PreparedStatement ps2 = Main.getPreparedStatement(stmt2);
ps2 = conn.prepareStatement(stmt2);
ps2.setInt(1, user.getId());
ps2.executeUpdate();
System.out.println("Deleted");

I don't understand how I delete from APP.DATAVAULT as well as APP.PERSON. As you can see there is a foreign key within APP.DATAVAULT which is a users id.

I have tried many things such as:

String stmt2 = "DELETE FROM APP.PERSON, APP.DATAVAULT WHERE PID = ?";

and

String stmt2 = "DELETE FROM APP.PERSON AND APP.DATAVAULT WHERE PID = ?";

I understand that I must use the foreign key to delete from both, but I don't know how.

I don't think you can delete from multiple tables using single query, you may need to use multiple delete queries and wrap it in a transaction. — Pradeep Simha, Mar 18 '13 at 01:39
@Aiias - I don't think these are the same questions. Mine is a Java PreparedStatement not an SQL query/command. — John Vasiliou, Mar 18 '13 at 01:41
@PradeepSimha - I did that already but didn't really like it, thought there might have been a better solution. — John Vasiliou, Mar 18 '13 at 01:42
@Aiias: This is not a dupe, since that question is for MySQL — Dancrumb, Mar 18 '13 at 01:42
@JohnVasiliou, but you cannot just do it in single statement. You need to use transactions if you want to do multiple delete statements in atomic way. — Pradeep Simha, Mar 18 '13 at 01:46

score 5 · Accepted Answer · answered Mar 18 '13 at 01:41

5

Unfortunately, per the docs, you cannot delete multiple tables with a single SQL query in Derby.

You can with some other RDBMS packages, such as MySQL... just not Derby.

answered Mar 18 '13 at 01:41

Dancrumb

26,597
10
74
130

Delete from two tables in one statement

1 Answers1

Linked