问题描述
我正尝试一次从几个表中删除.我做了一些研究,并想出了这个
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`
但是,我遇到此错误
我以前从未做过跨表删除操作,所以我现在没有经验,并且一直坚持下去!
I've never done a cross table delete before, so I'm inexperienced and stuck for now!
我在做什么错了?
推荐答案
在DELETE
语句中使用JOIN
.
Use a JOIN
in the DELETE
statement.
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
...仅从pets_activities
请参见此.
对于单个表删除,但具有参照完整性,还有其他方法可以使用EXISTS
,NOT EXISTS
,IN
,NOT IN
等.但是,在上面的方法中,您指定了要从中删除哪些表在FROM
子句之前使用别名可以使您更轻松地摆脱一些紧要关头.在99%的情况下,我倾向于联系EXISTS
,然后在1%的情况下使用这种MySQL语法.
For single table deletes, yet with referential integrity, there are other ways of doing with EXISTS
, NOT EXISTS
, IN
, NOT IN
and etc. But the one above where you specify from which tables to delete with an alias before the FROM
clause can get you out of a few pretty tight spots more easily. I tend to reach out to an EXISTS
in 99% of the cases and then there is the 1% where this MySQL syntax takes the day.
这篇关于如何从MySQL中的多个表中删除?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!