删除查询以删除无约束的条目

问题描述：

我有一张表说table1有50条记录，table1的记录与其他子表使用约束绑定。删除查询以删除无约束的条目

不是所有的50个记录有约束，有可能是一些记录（比如15）无约束的，所以我要运行删除查询删除15项单独出总50

我试过delete ignore声明：

delete ignore from table1;

，但它并没有帮助&我得到这个错误：

Cannot delete or update a parent row: a foreign key constraint fails

什么是最好的方式ACC在mysql查询中忽略这个？

你能简单地删除其中的FK为空？ – 2011-06-14 22:22:24

任何示例示例？ – Sharpeye500 2011-06-14 22:29:48

答

DELETE FROM table1 WHERE NOT EXISTS (SELECT * FROM details_table d WHERE d.table1_id = table1.id)

我有多个子表，而不是一个。 – Sharpeye500 2011-06-14 22:29:09

我想你需要为每个孩子添加“ADD NOT EXISTS”（或者使用一堆左连接）来过滤没有子行的记录。在这种情况下，“删除IGNORE”似乎不起作用。我刚刚在MySQL 5.5上试了一下 - 它不显示错误，但同时它也不会删除任何行。 – a1ex07 2011-06-14 22:34:41

为了更加准确，在mysql 5.5中，''DELETE IGNORE'一旦遇到违反约束的第一行就会停止删除行。所以一些记录将被删除。 – a1ex07 2011-06-14 22:39:40

答

这里有一个简单的，可读的，高效的查询，将你做它：

DELETE FROM table1 
WHERE id NOT IN (
    SELECT table1_id FROM details_table_1 
    UNION 
    SELECT table1_id FROM details_table_2 
    -- more unions to other child tables as required 
);

答

我一直喜欢加入到使用子查询IN()：

http://dev.mysql.com/doc/refman/5.5/en/rewriting-subqueries.html

Sometimes there are other ways to test membership in a set of values than by using a subquery. Also, on some occasions, it is not only possible to rewrite a query without a subquery, but it can be more efficient to make use of some of these techniques rather than to use subqueries. One of these is the IN() construct.

.. 。

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things. Today, MySQL Server and many other modern database systems offer a wide range of outer join types.

以下是如何回答与LEFT OUTER JOIN你的问题：

DELETE FROM table1 
LEFT OUTER JOIN child_table_1 c1 ON table1.id = c1.table_1_id 
LEFT OUTER JOIN child_table_2 c2 ON table1.id = c2.table_1_id 
-- More joins for additional child tables here 
WHERE c1.table_1_id IS NULL 
AND c2.table_1_id IS NULL 
-- AND other child tables 
;

删除查询以删除无约束的条目

相关推荐