使用子查询作为条件的 MySQLDELETEFROM

我试着做这样一个查询:

DELETE FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN (
SELECT DISTINCT(th1.tid)
FROM term_hierarchy AS th1
INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th1.parent = 1015
);

正如您可能看到的，如果同一个 tid 有其他的父关系，我想删除与1015的父关系。但是，这会产生一个语法错误:

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 'AS th
WHERE th.parent = 1015 AND th.tid IN (
SELECT DISTINCT(th1.tid)
FROM ter' at line 1

我已经检查了文档，并且自己运行了子查询，所有这些似乎都已经检查完毕。有人知道这里出了什么问题吗？

更新 : 如下所述，MySQL 不允许在子查询中使用您正在删除的表。

172200 次浏览
小开

You need to refer to the alias again in the delete statement, like:

DELETE th FROM term_hierarchy AS th
....

As outlined here in MySQL docs.

小开
最佳答案

You cannot specify target table for delete.

A workaround

create table term_hierarchy_backup (tid int(10)); <- check data type


insert into term_hierarchy_backup
SELECT DISTINCT(th1.tid)
FROM term_hierarchy AS th1
INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th1.parent = 1015;


DELETE FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN (select tid from term_hierarchy_backup);
小开

The alias should be included after the DELETE keyword:

DELETE th
FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN
(
SELECT DISTINCT(th1.tid)
FROM term_hierarchy AS th1
INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th1.parent = 1015
);
小开

For others that find this question looking to delete while using a subquery, I leave you this example for outsmarting MySQL (even if some people seem to think it cannot be done):

DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
FROM tableE
WHERE arg = 1 AND foo = 'bar');

will give you an error:

ERROR 1093 (HY000): You can't specify target table 'e' for update in FROM clause

However this query:

DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
FROM (SELECT id
FROM tableE
WHERE arg = 1 AND foo = 'bar') x);

will work just fine:

Query OK, 1 row affected (3.91 sec)

Wrap your subquery up in an additional subquery (here named x) and MySQL will happily do what you ask.

小开

I approached this in a slightly different way and it worked for me;

I needed to remove secure_links from my table that referenced the conditions table where there were no longer any condition rows left. A housekeeping script basically. This gave me the error - You cannot specify target table for delete.

So looking here for inspiration I came up with the below query and it works just fine. This is because it creates a temporary table sl1 that is used as the reference for the DELETE. 

DELETE FROM `secure_links` WHERE `secure_links`.`link_id` IN
(
SELECT
`sl1`.`link_id`
FROM
(
SELECT


`sl2`.`link_id`


FROM
`secure_links` AS `sl2`
LEFT JOIN `conditions` ON `conditions`.`job` = `sl2`.`job`


WHERE


`sl2`.`action` = 'something' AND
`conditions`.`ref` IS NULL
) AS `sl1`
)

Works for me.

小开

Isn't the "in" clause in the delete ... where, extremely inefficient, if there are going to be a large number of values returned from the subquery? Not sure why you would not just inner (or right) join back against the original table from the subquery on the ID to delete, rather than us the "in (subquery)".?

DELETE T FROM Target AS T
RIGHT JOIN (full subquery already listed for the in() clause in answers above) ` AS TT ON (TT.ID = T.ID)

And maybe it is answered in the "MySQL doesn't allow it", however, it is working fine for me PROVIDED I make sure to fully clarify what to delete (DELETE T FROM Target AS T). Delete with Join in MySQL clarifies the DELETE / JOIN issue.

小开

If you want to do this with 2 queries, you can always do something similar to this:

1) grab ids from the table with:

SELECT group_concat(id) as csv_result FROM your_table WHERE whatever = 'test' ...

Then copy result with mouse/keyboard or programming language to XXX below:

2) DELETE FROM your_table WHERE id IN ( XXX )

Maybe you could do this in one query, but this is what I prefer.

小开

@CodeReaper, @BennyHill: It works as expected.

However, I wonder the time complexity for having millions of rows in the table? Apparently, it took about 5ms to execute for having 5k records on a correctly indexed table.

My Query:

SET status = '1'
WHERE id IN (
SELECT id
FROM (
SELECT c2.id FROM clusters as c2
WHERE c2.assign_to_user_id IS NOT NULL
AND c2.id NOT IN (
SELECT c1.id FROM clusters AS c1
LEFT JOIN cluster_flags as cf on c1.last_flag_id = cf.id
LEFT JOIN flag_types as ft on ft.id = cf.flag_type_id
WHERE ft.slug = 'closed'
)
) x)```


Or is there something we can improve on my query above?
小开

you can use the alias in this way on the delete statement

DELETE  th.*
FROM term_hierarchy th
INNER JOIN term_hierarchy th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th.parent = 1015;

提交答案