删除同一表中的语句 [英] Delete statement in a same table

问题描述

我需要根据相关子查询来自同一表的列条件来查询同一表的delete语句.

I need to query a delete statement for the same table based on column conditions from the same table for a correlated subquery.

我无法直接运行delete语句并为相关子查询检查mysql中同一表的条件.

I can't directly run a delete statement and check a condition for the same table in mysql for a correlated subquery.

我想知道使用临时表是否会影响mysql的内存/性能?

I want to know whether using temp table will affect mysql's memory/performance?

任何帮助将不胜感激.

谢谢.

推荐答案

您的问题确实不清楚，但是我想您有一个相关的子查询，并且在从被锁定的同一张表中执行SELECT时遇到了麻烦删除.例如删除文档的所有内容，但最新版本除外:

Your question is really not clear, but I would guess you have a correlated subquery and you're having trouble doing a SELECT from the same table that is locked by the DELETE. For instance to delete all but the most recent revision of a document:

DELETE FROM document_revisions d1 WHERE edit_date < 
  (SELECT MAX(edit_date) FROM document_revisions d2 
   WHERE d2.document_id = d1.document_id);

这是MySQL的问题.

This is a problem for MySQL.

可以使用MySQL多表删除语法解决这类问题的许多示例:

Many examples of these types of problems can be solved using MySQL multi-table delete syntax:

DELETE d1 FROM document_revisions d1 JOIN document_revisions d2 
  ON d1.document_id = d2.document_id AND d1.edit_date < d2.edit_date;

但是这些解决方案最好是根据具体情况设计的，因此，如果您编辑问题并更具体地说明要解决的问题，也许我们可以为您提供帮助.

But these solutions are best designed on a case-by-case basis, so if you edit your question and be more specific about the problem you're trying to solve, perhaps we can help you.

在其他情况下，您可能是对的，使用临时表是最简单的解决方案.

In other cases you may be right, using a temp table is the simplest solution.

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