如果至少有一个值等于value，则设置为value

Question

我正试图通过查询教授学位的不同值来更新一个表。教员表有一个终端学位字段(值Y或N(。教授可以有多个学位，但如果至少有一行的最终学位=Y，我想将值设置为"终端"。如果只有"N"的值，而没有"Y"的值那么我想将终端度设置为"非终端">

以下是我目前拥有的代码，但它不适用于拥有一个以上学位(Y或N值(的教授

UPDATE SNP_FACULTY_CENSUS SFC
SET TERMINAL_DEGREE = (  
Case
WHEN EXISTS (SELECT DISTINCT(F.TERMINAL_DEGREE) 
FROM FACULTY F
left JOIN SNP_FACULTY_CENSUS S
ON F.PERSON_SKEY = S.PERSON_sKEY
)
THEN (SELECT  -- USE TERMINAL VALUE FROM FACULTY TABLE
CASE
WHEN F.TERMINAL_DEGREE = 'Y' AND F.TERMINAL_DEGREE = ''--if at least ONE value equals Y
THEN
'Terminal'
WHEN F.TERMINAL_DEGREE = 'Y' -- if all values equal Y
THEN 'Terminal'     
WHEN F.TERMINAL_DEGREE = 'N' --if ALL values equal N    
THEN
'Not terminal'
ELSE
'Unknown'
END
FROM FACULTY F
left JOIN SNP_FACULTY_CENSUS S
ON F.PERSON_SKEY = S.PERSON_sKEY)                        


END 
) 
WHERE SFC.OIR_FALL_TERM = 'Fall 2019'
;

Answer 1

您必须使用不存在，下面的查询应该可以工作，因为我没有样本数据，我无法测试它：

UPDATE SNP_FACULTY_CENSUS SFC
SET TERMINAL_DEGREE = (  
Case
WHEN EXISTS (SELECT DISTINCT F.TERMINAL_DEGREE 
FROM FACULTY F
left JOIN SNP_FACULTY_CENSUS S
ON F.PERSON_SKEY = S.PERSON_sKEY
)
THEN (SELECT  -- USE TERMINAL VALUE FROM FACULTY TABLE
CASE
WHEN F.TERMINAL_DEGREE = 'Y' --if ONE value equals Y
THEN
'Terminal'
WHEN F.TERMINAL_DEGREE = 'N' and not exists(select 1 from  FACULTY fa where F.PERSON_SKEY = fa.PERSON_sKEY and Fa.TERMINAL_DEGREE = 'N')--if ALL values equal N    
THEN
'Not terminal'
ELSE
'Unknown'
END
FROM FACULTY F
left JOIN SNP_FACULTY_CENSUS S
ON F.PERSON_SKEY = S.PERSON_sKEY)                        

END 
) 
WHERE SFC.OIR_FALL_TERM = 'Fall 2019';