MySQL 如何从MySQL中的多个表中删除？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3331992/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete from multiple tables in MySQL?
提问by alex
I am trying to delete from a few tables at once. I've done a bit of research, and came up with this
我试图一次从几个表中删除。我做了一些研究，并提出了这个
DELETE FROM `pets` p,
`pets_activities` pa
WHERE p.`order` > :order
AND p.`pet_id` = :pet_id
AND pa.`id` = p.`pet_id`
However, I am getting this error
但是，我收到此错误
Uncaught Database_Exception [ 1064 ]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p,
pets_activitiespa...
未捕获的 Database_Exception [1064]：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'p,
pets_activitiespa...附近使用的正确语法。
I've never done a cross table delete before, so I'm inexperienced and stuck for now!
我以前从未做过交叉表删除，所以我现在没有经验并且卡住了！
What am I doing wrong?
我究竟做错了什么？
回答by cadman
Use a JOINin the DELETEstatement.
JOIN在DELETE语句中使用 a 。
DELETE p, pa
FROM pets p
JOIN pets_activities pa ON pa.id = p.pet_id
WHERE p.order > :order
AND p.pet_id = :pet_id
Alternatively you can use...
或者，您可以使用...
DELETE pa
FROM pets_activities pa
JOIN pets p ON pa.id = p.pet_id
WHERE p.order > :order
AND p.pet_id = :pet_id
...to delete only from pets_activities
...只删除 pets_activities
See this.
看到这个。
For single table deletes, yet with referential integrity, there are other ways of doing with EXISTS, NOT EXISTS, IN, NOT INand etc. But the one above where you specify from which tables to delete with an alias before the FROMclause can get you out of a few pretty tight spots more easily. I tend to reach out to an EXISTSin 99% of the cases and then there is the 1% where this MySQL syntax takes the day.
对于单表中删除，但具有参照完整性，也有与做的其他方式EXISTS，NOT EXISTS，IN，NOT IN等，但上面你来自哪里，用别名来删除该表指定前一个FROM条款可以让你出几个漂亮的紧更容易出现斑点。我倾向于接触EXISTS99% 的情况，然后有 1% 的情况下使用这种 MySQL 语法。
回答by paxdiablo
Since this appears to be a simple parent/child relationship between petsand pets_activities, you would be better off creating your foreign key constraint with a deleting cascade.
由于这似乎是pets和之间的简单父/子关系pets_activities，因此最好使用删除级联来创建外键约束。
That way, when a petsrow is deleted, the pets_activitiesrows associated with it are automatically deleted as well.
这样，当pets一行被删除时，pets_activities与其关联的行也会被自动删除。
Then your query becomes a simple:
然后您的查询变得简单：
delete from `pets`
where `order` > :order
and `pet_id` = :pet_id
回答by RN Kushwaha
Use this
用这个
DELETE FROM `articles`, `comments`
USING `articles`,`comments`
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4
or
或者
DELETE `articles`, `comments`
FROM `articles`, `comments`
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4
回答by Brandon Horsley
I don't have a mysql database to test on at the moment, but have you tried specifying what to delete prior to the from clause? For example:
我目前没有要测试的 mysql 数据库，但是您是否尝试过在 from 子句之前指定要删除的内容？例如：
DELETE p, pa FROM `pets` p,
`pets_activities` pa
WHERE p.`order` > :order
AND p.`pet_id` = :pet_id
AND pa.`id` = p.`pet_id`
I think the syntax you used is limited to newer versions of mysql.
我认为您使用的语法仅限于较新版本的 mysql。
回答by Andre Gallo
回答by Kalesh Kaladharan
To anyone reading this in 2017, this is how I've done something similar.
对于在 2017 年读到这篇文章的人来说，这就是我做类似事情的方式。
DELETE pets, pets_activities FROM pets inner join pets_activities
on pets_activities.id = pets.id WHERE pets.`order` > :order AND
pets.`pet_id` = :pet_id
Generally, to delete rows from multiple tables, the syntax I follow is given below. The solution is based on an assumption that there is some relation between the two tables.
通常，要从多个表中删除行，我遵循的语法如下。该解决方案基于两个表之间存在某种关系的假设。
DELETE table1, table2 FROM table1 inner join table2 on table2.id = table1.id
WHERE [conditions]
回答by Ravi Makwana
I found this article which showing you how to delete data from multiple tables by using MySQL DELETE JOINstatement with good explanation.
我发现这篇文章向您展示了如何使用MySQL DELETE JOIN语句从多个表中删除数据，并有很好的解释。
