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

2022-01-30 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`

但是，我收到了这个错误

However, I am getting this error

未捕获的 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.

相关文章