一次删除多个表中的行 [英] Delete rows from multiple tables at once

问题描述

我正在尝试将2个查询合并为一个

I'm trying to combine 2 queries into one like this

$result=$db->query ("DELETE FROM menu WHERE name = '$new'") or die($db->error);
$result=$db->query ("DELETE FROM pages WHERE title = 'new'") or die($db->error);

进入

$result=$db->query ("DELETE FROM menu AS m, pages AS p WHERE m.name = 'new' AND p.title='new'") or die($db->error);

但是DB给出了语法错误.怎么了?

But DB gives syntax error. What's wrong?

推荐答案

您确定可以

You sure well can delete from multiple tables in one statement with MySQL. Your requirements would work using something like the following query:

DELETE
    menu, pages
FROM
    menu JOIN pages
WHERE
    menu.name = 'some name' AND
    pages.title = 'some title'

或者:

DELETE FROM
    menu, pages
USING
    menu JOIN pages
WHERE
    menu.name = 'some name' AND
    pages.title = 'some title'

这些示例有一个问题:两个值必须都存在才能使其正常工作.

There's one catch with these examples though: both values must exist for this to work.

我认为应该有更多的方法来获得理想的结果，没有这种限制(我会使用其他类型的JOIN)，但是我一直无法弄清楚应该如何工作.

There should be more ways to get the desired result I think, without this constraint (using other types of JOINs I would think) but I haven't been able to figure out how that should work.

如果您要删除的行通过外键以某种方式彼此链接(并且您没有在InnoDB表中使用外键约束)，那么做您想做的事情应该更加容易.这样的事情应该可以工作了:

If the rows you want to delete are somehow linked to each other with foreign keys (and you are not using foreign key constraints with InnoDB tables), it should be even more easy to do what you want. Something like this should work then:

DELETE
    menu, pages
FROM
    menu LEFT JOIN pages
    ON menu.pageId = page.id # using imagined columns here
WHERE # this where clause might be redundant then
    menu.name = 'some name' AND
    pages.title = 'some title'

但是请谨慎使用这些示例.首先，我要在测试环境中进行实验，因为我不确定100％的确切含义是诚实的；我只是想提供一个提示，即可以 一次从多个表中删除.

Be careful with these examples though. Experiment on a test environment first, as I'm not sure 100% of the exact implications to be honest; I just wanted to provide a hint for the fact that deleting from multiple tables at once is possible.

这篇关于一次删除多个表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！