MySQL 同一个表中的delete语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3346068/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete statement in a same table
提问by Sharpeye500
I need to query a delete statement for the same table based on column conditions from the same table for a correlated subquery.
我需要根据相关子查询的同一表中的列条件查询同一表的删除语句。
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?
我想知道使用临时表是否会影响mysql的内存/性能？
Any help will be highly appreciated.
任何帮助将不胜感激。
Thanks.
谢谢。
回答by Adam Lane
You can make mysql do the temp table for you by wrapping your "where" query as an inline from table.
您可以通过将“where”查询包装为表中的内联来让 mysql 为您执行临时表。
This original query will give you the dreaded "You can't specify target table for update in FROM clause":
这个原始查询会给你一个可怕的“你不能在 FROM 子句中指定更新的目标表”：
DELETE FROM sametable
WHERE id IN (
SELECT id FROM sametable WHERE stuff=true
)
Rewriting it to use inline temp becomes...
重写它以使用内联临时变得......
DELETE FROM sametable
WHERE id IN (
SELECT implicitTemp.id from (SELECT id FROM sametable WHERE stuff=true) implicitTemp
)
回答by Bill Karwin
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 锁定的同一个表中执行 SELECT 时遇到问题。例如，要删除文档的除最新修订之外的所有内容：
DELETE FROM document_revisions d1 WHERE edit_date <
(SELECT MAX(edit_date) FROM document_revisions d2
WHERE d2.document_id = d1.document_id);
This is a problem for MySQL.
这对 MySQL 来说是个问题。
Many examples of these types of problems can be solved using MySQL multi-table delete syntax:
使用 MySQL 多表删除语法可以解决此类问题的许多示例：
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.
在其他情况下，您可能是对的，使用临时表是最简单的解决方案。
回答by dcp
can't directly run a delete statement and check a condition for the same table
不能直接运行删除语句并检查同一个表的条件
Sure you can. If you want to delete from table1 while checking the condition that col1 = 'somevalue', you could do this:
你当然可以。如果您想在检查条件时从 table1 中删除col1 = 'somevalue'，您可以这样做：
DELETE
FROM table1
WHERE col1 = 'somevalue'
EDIT
To delete using a correlated subquery, please see the following example:
编辑
要使用相关子查询删除，请参见以下示例：
create table project (id int);
create table emp_project (id int, project_id int);
insert into project values (1);
insert into project values (2);
insert into emp_project values (100, 1);
insert into emp_project values (200, 1);
/* Delete any project record that doesn't have associated emp_project records */
DELETE
FROM project
WHERE NOT EXISTS
(SELECT *
FROM emp_project e
WHERE e.project_id = project.id);
/* project 2 doesn't have any emp_project records, so it was deleted, now
we have 1 project record remaining */
SELECT * FROM project;
Result:
id
1
回答by Carter Medlin
Create a temp table with the values you want to delete, then join it to the table while deleting. In this example I have a table "Games" with an ID column. I will delete ids greater than 3. I will gather the targets in a temp table first so I can report on them later.
使用要删除的值创建一个临时表，然后在删除时将其加入表中。在这个例子中，我有一个带有 ID 列的“游戏”表。我将删除大于 3 的 id。我将首先在临时表中收集目标，以便稍后报告它们。
DECLARE @DeletedRows TABLE (ID int)
insert
@DeletedRows
(ID)
select
ID
from
Games
where
ID > 3
DELETE
Games
from
Games g
join
@DeletedRows x
on x.ID = g.ID
回答by Felix Kling
You mean something like:
你的意思是这样的：
DELETE FROM table WHERE someColumn = "someValue";
?
?
This is definitely possible, read about the DELETEsyntax in the reference manual.
这绝对是可能的，请阅读DELETE参考手册中的语法。
回答by YoK
You can delete from same table. Delete statement is as follows
您可以从同一个表中删除。删除语句如下
DELETE FROM table_name
WHERE some_column=some_value
