英文:
Updating values based on a group of records
问题
我正在尝试对“Keys”列执行更新，其中Janitor值设置为“No_Name”。只有当所有Janitors在Building Name列中都具有NULL值时，才应将其更新为“None”。当Janitor（们）确实具有Key时，将“No_Name”设置为NULL。
| Building Name | Janitor | Keys | Notes |
|---|---|---|---|
| Building A | Andrew | Yes | |
| Building A | Mike | Yes | |
| Building A | Bill | NULL | |
| Building A | Phil | Yes | |
| Building A | No_Name | NULL | --- 应保持为NULL |
| Building B | Andrew | NULL | |
| Building B | Mike | NULL | |
| Building B | Bill | NULL | |
| Building B | Phil | NULL | |
| Building B | No_Name | NULL | --- 应更改为'NONE'，因为没有Owners有Keys |
英文:
I'm trying to carry out an update on the "Keys" column, where Janitor value is set to "No_Name". It should be updated to "None" only when all Janitors have a NULL value per Building Name. When a Janitor(s) do have a Key then set 'No_Name' as NULL still.
| Building Name | Janitor | Keys | Notes |
|---|---|---|---|
| Building A | Andrew | Yes | |
| Building A | Mike | Yes | |
| Building A | Bill | NULL | |
| Building A | Phil | Yes | |
| Building A | No_Name | NULL | --- should stay NULL |
| Building B | Andrew | NULL | |
| Building B | Mike | NULL | |
| Building B | Bill | NULL | |
| Building B | Phil | NULL | |
| Building B | No_Name | NULL | --- should change to 'NONE' bcs no Owners have the keys |
Tried a bunch of AND/OR statements with no success.
答案1
得分: 2
你可以使用可更新的表达式，利用窗口聚合功能：
update t
set Keys = case when k is null then 'None' end
from (
select *, Max(keys) over(partition by Building_Name) k
from t
)t
where Janitor = 'No_Name';
英文:
You could use an updatable table expression making use of a window aggregate:
update t
set Keys = case when k is null then 'None' end
from (
select *, Max(keys) over(partition by Building_Name) k
from t
)t
where Janitor = 'No_Name';
答案2
得分: 0
你可以尝试使用一个 EXISTS 表达式。
UPDATE tab
SET Keys = 'None'
WHERE Janitor = 'No_Name'
AND NOT EXISTS(SELECT 1
FROM tab t2
WHERE tab.BuildingName = t2.BuildingName AND t2.Keys IS NOT NULL);
输出：
| BuildingName | Janitor | Keys |
|---|---|---|
| Building A | Andrew | Yes |
| Building A | Mike | Yes |
| Building A | Bill | null |
| Building A | Phil | Yes |
| Building A | No_Name | null |
| Building B | Andrew | null |
| Building B | Mike | null |
| Building B | Bill | null |
| Building B | Phil | null |
| Building B | No_Name | None |
查看演示 这里。
英文:
You can try going with an EXISTS expression.
UPDATE tab
SET Keys = 'None'
WHERE Janitor = 'No_Name'
AND NOT EXISTS(SELECT 1
FROM tab t2
WHERE tab.BuildingName = t2.BuildingName AND t2.Keys IS NOT NULL);
Output:
| BuildingName | Janitor | Keys |
|---|---|---|
| Building A | Andrew | Yes |
| Building A | Mike | Yes |
| Building A | Bill | null |
| Building A | Phil | Yes |
| Building A | No_Name | null |
| Building B | Andrew | null |
| Building B | Mike | null |
| Building B | Bill | null |
| Building B | Phil | null |
| Building B | No_Name | None |
Check the demo here.
答案3
得分: 0
我不知道您正在使用的确切数据库管理系统，但思想通常是相同的。
您可以使用聚合函数如MAX()来生成要更新为None的建筑列表。
select "Building name"
from No_Name
group by "Building name"
having max(keys) <> NULL
此查询将为您提供所有在keys字段中具有所有NULL值的建筑物，然后您可以将其整合到更新语句中。
英文:
I dont know the exact DBMS you're using, but the idea is generally the same.
You can use aggregation function like MAX() on Keys to generate list of buildings to update to None.
select "Building name"
from No_Name
group by "Building name"
having max(keys) <> NULL
this query will give you all building that have all NULL values in keys, then you can incorporate it in Update statement.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论