在一条语句中从两个表中删除 [英] Delete from two tables in one statement

问题描述

我希望能够从2个表中删除所有数据，其中用户ID =给定的ID。我正在使用Java，Derby DB，Netbeans 7.3 Beta 2和OS X Mountain Lion。

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):

到目前为止，这是我的声明：

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

我不知道如何从APP.DATAVAULT和APP.PERSON中删除。如您所见，APP.DATAVAULT中有一个外键，它是一个用户ID。

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 = ?";

和

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.

推荐答案

不幸的是，每文档，您无法在Derby中使用单个SQL查询删除多个表。

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

您可以与其他RDBMS软件包一起使用，例如 MySQL ...只是非德比。

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

这篇关于在一条语句中从两个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！