如何精简UPDATE语句？将MovieSurvey表多字段的'Did not see'设为NULL

可以用单条UPDATE语句一次性处理所有目标字段，避免重复执行多条语句。利用CASE表达式（或数据库对应的条件函数）判断字段值，仅当值为'Did not see'时更新为NULL，否则保留原字段值：

UPDATE MovieSurvey
SET 
    field7 = CASE WHEN field7 = 'Did not see' THEN NULL ELSE field7 END,
    field8 = CASE WHEN field8 = 'Did not see' THEN NULL ELSE field8 END,
    field9 = CASE WHEN field9 = 'Did not see' THEN NULL ELSE field9 END,
    field10 = CASE WHEN field10 = 'Did not see' THEN NULL ELSE field10 END,
    field11 = CASE WHEN field11 = 'Did not see' THEN NULL ELSE field11 END,
    field12 = CASE WHEN field12 = 'Did not see' THEN NULL ELSE field12 END
WHERE 
    field7 = 'Did not see' 
    OR field8 = 'Did not see' 
    OR field9 = 'Did not see' 
    OR field10 = 'Did not see' 
    OR field11 = 'Did not see' 
    OR field12 = 'Did not see';

说明 #

• CASE表达式：对每个字段单独判断，仅匹配目标值时才更新为NULL，不匹配则保留原内容，避免误改其他值。
• WHERE子句：过滤出至少有一个目标字段符合条件的行，减少不必要的行更新，提升执行效率。

如果你的数据库支持更简洁的条件函数（比如MySQL的IF()），也可以替换成更短的写法：

UPDATE MovieSurvey
SET 
    field7 = IF(field7 = 'Did not see', NULL, field7),
    field8 = IF(field8 = 'Did not see', NULL, field8),
    field9 = IF(field9 = 'Did not see', NULL, field9),
    field10 = IF(field10 = 'Did not see', NULL, field10),
    field11 = IF(field11 = 'Did not see', NULL, field11),
    field12 = IF(field12 = 'Did not see', NULL, field12)
WHERE 
    field7 = 'Did not see' 
    OR field8 = 'Did not see' 
    OR field9 = 'Did not see' 
    OR field10 = 'Did not see' 
    OR field11 = 'Did not see' 
    OR field12 = 'Did not see';

内容的提问来源于stack exchange，提问作者g.senorsenor