如何从MySQL中的多个表中删除?

2021-11-20 00:00:00 sql mysql sql-delete mysql-error-1064

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

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

或者你可以使用...

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.

相关文章