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.
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.