您无法在FROM子句中指定目标表"NAME"进行更新 [英] You can't specify target table 'NAME' for update in FROM clause

问题描述

我正在尝试对表中的数据应用此解决方案在具有重复项的MySQL DB中.我得到这样的错误:

Im trying to apply this solution to my data in table in MySQL DB which has duplicates. And I get such error:

SQL Error [1093] [HY000]: You can't specify target table 'NAME' for update in FROM clause


DELETE NAME FROM NAME
WHERE NAME.id NOT IN
   (SELECT MIN(id) 
   FROM NAME GROUP BY col1, col2)

也尝试过分配别名-但没有成功. 出现此错误的原因是什么? 通常指出SQL脚本可以产生循环过程，但是在这里我实际上看不到任何相关信息-很明显，DELETE和SELECT的两个选择是分离的-引擎必须首先执行SELECT一次，然后在WHERE条件下将其用于DELETE. 那么-为什么会发生此错误，以及如何真正对表进行重复数据删除呢? =)

Also tried too assign aliases - but without success. What reason of this error here? It generally points that SQL script can produce cyclic process, but here I actually dont see any relevant to this - It is obvious that two selections for DELETE and for SELECT is detached - engine must do SELECT once firstly and then use it in WHERE conditions for DELETE. So - why this error happens and how can I actually deduplicate my table? =)

推荐答案

尝试一下可能会帮助您

DELETE  FROM NAME
WHERE NAME.id NOT IN (
                     SELECT * FROM ( 
                                    SELECT MIN(id)  FROM NAME GROUP BY col1,col2
                                   ) AS p 
                      ) 

了解详情

这篇关于您无法在FROM子句中指定目标表"NAME"进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！