blocks|key|3217443|text|您需要在delete语句中再次引用别名，例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3217444|DELETE+th+FROM+term_hierarchy+AS+th
....|code-block|syntax|javascript|3217445|As+outlined+here+in+MySQL+docs.|offset|length|3217446|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/delete.html^0|0|0|0|V|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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

<pre><code>DELETE th FROM term_hierarchy AS th
....
</code></pre>

<a href="http://dev.mysql.com/doc/refman/5.5/en/delete.html" rel="noreferrer">As outlined here in MySQL docs.</a>

blocks|key|285298|text|不能指定要删除的目标表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|285299|一种解决方法|285300|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);|code-block|syntax|javascript|285301|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You cannot specify target table for delete.

A workaround

<pre><code>create table term_hierarchy_backup (tid int(10)); &lt;- 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);
</code></pre>

blocks|key|287481|text|别名应包含在DELETE关键字之后：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|287482|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
);|code-block|syntax|javascript|287483|entityMap^0|6|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

The alias should be included after the <code>DELETE</code> keyword:

<pre><code>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
);
</code></pre>

blocks|key|3004035|text|对于在使用子查询时发现这个问题想要删除的其他人，我把这个例子留给你，让MySQL变得更聪明(即使有些人似乎认为它不可能做到)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3004036|DELETE+e.*
FROM+tableE+e
WHERE+id+IN+(SELECT+id
+++++++++++++FROM+tableE
+++++++++++++WHERE+arg+=+1+AND+foo+=+'bar');|code-block|syntax|javascript|3004037|会给你一个错误：|3004038|ERROR+1093+(HY000):+You+can't+specify+target+table+'e'+for+update+in+FROM+clause|3004039|但是，此查询：|3004040|DELETE+e.*
FROM+tableE+e
WHERE+id+IN+(SELECT+id
+++++++++++++FROM+(SELECT+id
+++++++++++++++++++FROM+tableE
+++++++++++++++++++WHERE+arg+=+1+AND+foo+=+'bar')+x);|3004041|将会工作得很好：|3004042|Query+OK,+1+row+affected+(3.91+sec)|3004043|将您的子查询包装在一个附加子查询(这里称为x)中，MySQL将很高兴地执行您所要求的操作。|3004044|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

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):

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

will give you an error:

<pre><code>ERROR 1093 (HY000): You can't specify target table 'e' for update in FROM clause
</code></pre>

However this query:

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

will work just fine:

<pre><code>Query OK, 1 row affected (3.91 sec)
</code></pre>

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

blocks|key|290827|text|我以一种略微不同的方式来处理这个问题，它对我很有效；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|290828|我需要从引用conditions表的表中删除secure_links，在这个表中不再剩下任何条件行。基本上就是一个内务脚本。这给了我一个错误-您不能指定要删除的目标表。|offset|length|style|CODE|290829|因此，为了寻找灵感，我想出了下面的查询，它运行得很好。这是因为它创建了一个临时表sl1，用作删除操作的引用。|290830|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`
++++++++++++)|code-block|syntax|javascript|290831|这对我很有效。|290832|entityMap^0|0|6|A|M|C|0|14|3|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@$D|Z|E|10|F|G]]|9|@]|A|$]]|$1|J|3|K|5|L|7|11|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|R|$]]

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

I needed to remove <code>secure_links</code> from my table that referenced the <code>conditions</code> 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 <code>sl1</code> that is used as the reference for the DELETE. 

<pre><code>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`
 )
</code></pre>

Works for me.

blocks|key|290834|text|是不是delete中的"+in“子句...如果要从子查询中返回大量的值，效率会非常低？不确定为什么您不从要删除的ID上子查询返回原始表的内部(或右)连接，而不是我们的"in+(+subquery+)“。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|290835|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)|code-block|syntax|javascript|290836|也许答案是"MySQL不允许它“，但是，如果我确保完全弄清楚要删除什么(将T从目标中删除为T)，那么它对我来说工作得很好。Delete+with+Join+in+MySQL澄清了删除/加入问题。|offset|length|290837|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/652770/delete-with-join-in-mysql^0|0|0|1P|P|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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)".?

<pre><code>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)
</code></pre>

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). <a href="https://stackoverflow.com/questions/652770/delete-with-join-in-mysql">Delete with Join in MySQL</a> clarifies the DELETE / JOIN issue.

blocks|key|287524|text|如果你想用两个查询来做这件事，你总是可以做类似这样的事情：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|287525|1)通过以下方式从表中抓取ids：|287526|SELECT+group_concat(id)+as+csv_result+FROM+your_table+WHERE+whatever+=+'test'+...|code-block|syntax|javascript|287527|然后用鼠标/键盘或编程语言将结果复制到下面的XXX：|287528|2)+DELETE+FROM+your_table+WHERE+id+IN+(+XXX+)|287529|也许你可以在一个查询中做到这一点，但这是我更喜欢的。|287530|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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

1) grab ids from the table with:

<pre><code>SELECT group_concat(id) as csv_result FROM your_table WHERE whatever = 'test' ...
</code></pre>

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

<pre><code>2) DELETE FROM your_table WHERE id IN ( XXX )
</code></pre>

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

blocks|key|288435|text|@CodeReaper，@BennyHill:它的工作方式与预期一致。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|288436|但是，我想知道表中有数百万行的时间复杂性？显然，在一个正确的索引表上有5k条记录需要大约5ms的执行时间。|offset|length|style|CODE|288437|我的问题是：|288438|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?|code-block|syntax|javascript|288439|entityMap^0|0|18|3|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|V|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

@CodeReaper, @BennyHill: 
It works as expected. 

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

My Query:

<pre><code>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?
</code></pre>

blocks|key|290949|text|您可以在delete语句中以这种方式使用别名|type|unstyled|depth|inlineStyleRanges|entityRanges|data|290950|DELETE++th.*
FROM+term_hierarchy+th
INNER+JOIN+term_hierarchy+th2+ON+(th1.tid+=+th2.tid+AND+th2.parent+!=+1015)
WHERE+th.parent+=+1015;|code-block|syntax|javascript|290951|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

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

I am trying to do a query like this:

<pre><code>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
);
</code></pre>

As you can probably tell, I want to delete the parent relation to 1015 if the same tid has other parents. However, that yields me a syntax error:

<pre><code>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
</code></pre>

I have checked the documentation, and run the subquery by itself, and it all seems to check out. Can anyone figure out what's wrong here?

Update: As answered below, MySQL does not allow the table you're deleting from be used in a subquery for the condition.

MySQL DELETE FROM with subquery as condition

MySQL

我正在尝试执行这样的查询：DELETE FROM term_hierarchy AS thWHERE th.parent = 1015 AND th.tid IN (    SELECT DISTINCT(th1.tid)    FROM term_hierarchy AS th1    INNER JOIN term...

问MySQL DELETE FROM with subquery as条件
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL DELETE FROM with subquery as条件EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL DELETE FROM with subquery as条件
EN