問題描述

我試圖一次從幾個(gè)表中刪除.我做了一些研究，并提出了這個(gè)

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`

但是，我收到此錯(cuò)誤

未捕獲的 Database_Exception [1064]:您的 SQL 語法有錯(cuò)誤；檢查與您的 MySQL 服務(wù)器版本相對應(yīng)的手冊，了解在 '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...

我以前從來沒有做過交叉表刪除，所以我現(xiàn)在沒有經(jīng)驗(yàn)，卡住了！

I've never done a cross table delete before, so I'm inexperienced and stuck for now!

我做錯(cuò)了什么?

推薦答案

在 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

參見這個(gè).

對于單表刪除，但具有參照完整性，還有其他方法可以使用 EXISTS、NOT EXISTS、IN、NOT IN 等等.但是上面的那個(gè)你在FROM 子句之前用別名指定要從哪些表中刪除可以讓你更容易地?cái)[脫一些非常緊張的地方.我傾向于在 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.

這篇關(guān)于如何從MySQL中的多個(gè)表中刪除?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！