在某些条件下将重复值设置为NULL

Question

我有一个相当大的SQL数据库(目前是SQLite3)，只有一个关系：

CREATE TABLE sometable (
    `name` TEXT,
    `position` INTEGER
);

由于数据的性质，没有主键或约束，只有name和position列上的非唯一索引。现在，我需要将name列设置为NULL，其中名称是重复的，但位置不是。重复的(name,position)对是可以的，不应更改。

在此之前：

name | position
-----+---------
a    | 5
a    | 5
b    | 7
b    | 8
c    | 6
c    | 7
c    | 7
d    | 6

之后：

name | position
-----+---------
a    | 5
a    | 5
NULL | 6
NULL | 7
NULL | 6
NULL | 7
NULL | 8
d    | 6

我可以保留的唯一行是名称/职位关联中不存在歧义的行。但是，必须保留重复的名称/位置对，只要该名称不与其他位置相关联。

我找不到合适的SQL语句来做到这一点。

Answer 1

尝试使用关联子查询来检查给定名称是否应由NULL替换的更新。下面的子查询按名称聚合，然后检查是否有多个职位。如果是这样，则该名称将是更新的候选名称。

UPDATE sometable
SET name = NULL
WHERE EXISTS (SELECT name FROM sometable t2
              WHERE sometable.name = t2.name
              GROUP BY name
              HAVING COUNT(DISTINCT position) > 1);

Answer 2

您可以将union all与not exists/exists结合使用：

select t.name, t.position 
from table t
where not exists (select 1 from table t1 where t1.name = t.name and t.position <> t1.position)
union all
select null, t.position 
from table t
where exists (select 1 from table t1 where t1.name = t.name and t.position <> t1.position);

因此，update版本将是：

update table t
     set t.name = null
where exists (select 1 from table t1 where t1.name = t.name and t1.position <> t.position);