如何从MySQL中的多个表中删除? [英] How to delete from multiple tables in MySQL?

问题描述

我正尝试一次从几个表中删除.我做了一些研究，并想出了这个

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`

但是，我遇到此错误

未捕获的Database_Exception [1064]:您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在'p，pets_activities pa ...

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_activities pa...

我以前从未做过跨表删除操作，所以我现在没有经验，并且一直坚持下去！

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中的多个表中删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！