无法指定mysql中更新、删除查询的目标表

发布于 2024-12-14 19:12:49 字数 390 浏览 0 评论 0原文

我想做的事情看起来像是在 mysql 中进行简单的删除，但遇到了一些麻烦。我想这样做：

delete from table where col_name not in (select distinct col_name from table);

该表没有唯一键。当我尝试这个时，我收到错误：

不能在from子句中指定更新的目标表；错误编号1093

mysql 社区 5.1

有没有办法保存此查询的输出 ->

select distinct col_name from table;

进入温度。表并在删除查询中使用它？

原文

I want to do what seems like a simple delete in mysql but am having some trouble. I want to do this:

delete from table where col_name not in (select distinct col_name from table);

This table does not have a unique key. When I try this, I get the error:

You can't specify target table for update in from clause; ErrorNr. 1093

mysql community 5.1

Is there a way i can save the output from this query ->

select distinct col_name from table;

into temp. table and use it in the delete query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

看春风乍起 2024-12-21 19:12:49

您必须使用别名。

delete from table where col_name not in (select a.col_name from (select distinct col_name from table) a);

它应该有效。

编辑：抱歉，我误解了这个问题，只关注 SQL 错误。上述请求没有回答删除没有唯一键的重复行的问题。

You must use an alias.

delete from table where col_name not in (select a.col_name from (select distinct col_name from table) a);

It should work.

Edit : sorry, I misunderstood the question, and only focus on the SQL Error. The probleme to delete duplicate line with no unique key isn't answered by the above request.

回复收藏 0 原文

捶死心动 2024-12-21 19:12:49

@Molochdaa 接受的答案是错误的

解释。
假设这是表

col_name |  value
------------------
east     |  10
west     |  15
east     |  10
west     |  15
north    |  5
east     |  10
north    |  5

select different col_name from table_name 给出：

col_name
------------------
east     
west     
north

现在，当您编写 delete from table where col_name not in ( ... ) 时，将不会返回任何行，因为每一列都在返回的列表

中正确的答案是（由@jeffery_the_wind建议）

delete from table where col_name in (
   select a.col_name from (
      select col_name from table group by col_name having count(*)>1
   ) a
)

另一个更简单的答案

delete from table_name
       where rowid not in ( select min(rowid) 
                            from Table_name
                            group by column1..,column2,...column3..)

the answer by @Molochdaa which is accepted is wrong

Explanation.
suppose this is the table

col_name |  value
------------------
east     |  10
west     |  15
east     |  10
west     |  15
north    |  5
east     |  10
north    |  5

select distinct col_name from table_name gives:

col_name
------------------
east     
west     
north

now when you write delete from table where col_name not in ( ... )then no row will be returned as every column is in the returned list

The correct answer would be (suggested by @jeffery_the_wind)

delete from table where col_name in (
   select a.col_name from (
      select col_name from table group by col_name having count(*)>1
   ) a
)

Another answer which is more simple

delete from table_name
       where rowid not in ( select min(rowid) 
                            from Table_name
                            group by column1..,column2,...column3..)

回复收藏 0 原文

~没有更多了~