如何使一个查询的结果在 NOT IN 子句中? [英] How to make the result of one query be in the NOT IN clause?

问题描述

我想删除不属于一组特定值的值.我事先不知道这些值.我可以通过查询它们来知道这些值 &我想一步完成这个过程.我做了以下声明:

I want to delete values that are not from a specific set of values. I don't know these values in advance. I can know these values by querying them & I want to do the process in one step. I did the following statement:

delete from db.table1 where domain not in 
(
select distinct domainname 
from db.table1
where domainname like '%.uci.edu'
group by keyvalue
order by domainname
) 
and domainanme like '%.uci.edu';

知道domainname是唯一字段，table1的主键是auto_increment号.

Knowing that the domainname is a unique field, and the primary key for table1 is auto_increment number.

我需要做的是:我有一个域名列表.说:aa.yahoo.com、bb.yahoo.com、cc.yahoo.com、aa.msn.com.每个域名都有一个键值.如果密钥是共享的，我想删除这条记录.因此，我选择了按键值分组的不同值.如果记录不在不同的集合中，并且具有相同的顶级域，那么我不需要它.

What I need to do is the following: I have a list of domain names. say: aa.yahoo.com, bb.yahoo.com, cc.yahoo.com, aa.msn.com. Each domain name has a key value. If the key is shared, I want to delete this record. So, I selected the distinct values grouped them by the key value. If the record in not in the distinct set, and have the same top level domain, then I don't need it.

我的查询可能有误.SQL 给了我 error: 1093: 你不能在 FROM 子句中指定目标表 table1 进行更新.

I might be wrong in my query. SQL gives me error: 1093: you can' specify target table table1 for update in FROM clause.

请以任何有效的方式帮助实现这一目标.我有一个很长的剪切顶级域名列表，我只需要具有不同键值的那些.如果有一个通用的解决方案可以做到这一点，而无需一个一个地处理每个域名(如我的例子)，那就更好了.

Please, help in achieving this purpose by any efficient way. I have a very long list of sheared top level domain names, I only need the ones with distinct key value. If there is a universal solution that will do this without the need of going on every domain name one by one (like my example) that is better.

domainname 不是唯一字段.我使用另一个字段:domainnameNo 作为唯一字段

domainname is NOT unique field. I use another field: domainnameNo as unique field

推荐答案

我认为这就是您要找的:

I think this is what you are looking for:

DELETE a
FROM db.table1 a
    INNER JOIN (SELECT id
                FROM db.table1
                WHERE domainname LIKE '%.uci.edu'
                GROUP BY keyvalue
                HAVING COUNT(1) > 1
        ) b
        ON a.id = b.id;

这篇关于如何使一个查询的结果在 NOT IN 子句中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！