SQL命令从表中删除数据id在两个表中的表

I am trying to delete a row in i.e. table 2 where the id is copied from table 1 query. I want to delete this row from table 2 and also from table 1 but I am having an issue where the command I have used all work but it does not seem delete from the tables. I believe this might be because of the relationship the tables have(I used mysql workbench to make the DB design)

我试图删除表2中从表1查询复制id的行。我想从表2和表1中删除此行，但我遇到的问题是我使用的命令全部工作但似乎没有从表中删除。我相信这可能是因为表的关系（我使用mysql workbench进行数据库设计）

I used this command :

我用这个命令：

delete
from doctorsTable
where Users_idUser in (select Users_idUser from Users where idUser = 20)

This is the relation :

这是关系：

As mentioned, I am trying to delete the row from doctorsTable with Users_idUser=20 and automatically it would delete from Users table idUser also with 20. I have tried the above command, it seems to run but its not really deleting the rows . please help !

如上所述，我试图删除来自doctorsTable的行与Users_idUser = 20并自动删除Users表idUser也是20.我已经尝试了上面的命令，它似乎运行但它并没有真正删除行。请帮忙！

1 个解决方案

#1

I think what you want to do is to delete all rows with idUser from both tables with one statement.

我想你想要做的是用一个语句从两个表中删除idUser的所有行。

You can do so by joining and deleting them like:

您可以通过加入和删除它们来实现：

DELETE doctorsTable, Users
FROM doctorsTable
INNER JOIN Users ON doctorsTable.Users_idUser = Users.idUser
WHERE doctorsTable.Users_idUser = 20

In the DELETE line, you specify the table(s) from which to delete matching rows.

在DELETE行中，指定要从中删除匹配行的表。

#1