sqlite 如何压缩这些set和where语句

fiei3ece 于 2023-02-09 发布在 SQLite

关注(0)|答案(2)|浏览(168)

我想将单个表中的所有'didn 't see'值更新为NULL。
我不知道如何编写一个set语句
这是我想写得更优雅的代码

UPDATE  MovieSurvey
   SET field7 =NULL
   where field7 ='Did not see'
 
UPDATE  MovieSurvey 
   SET field8 =NULL 
   where field8 ='Did not see'
  
 UPDATE  MovieSurvey 
   SET field9 =NULL 
   where field9 ='Did not see'
   
UPDATE  MovieSurvey 
   SET field10 =NULL 
   where field10 ='Did not see'

UPDATE  MovieSurvey 
   SET field11 =NULL 
   where field11 ='Did not see'

UPDATE  MovieSurvey 
   SET field12 =NULL 
   where field12 ='Did not see'

谢谢你的帮助！

sqlite

来源：https://stackoverflow.com/questions/75347385/how-do-i-condense-these-set-and-where-statements

2条答案

按热度按时间

vtwuwzda1#

您必须引用要更新的每一列，但是您 * 可以 * 在单个更新中使用case表达式。

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

赞(0）回复(0）举报 2023-02-09

uubf1zoe2#

使用函数NULLIF()，如果列值为'Did not see'，则返回NULL，否则返回列的实际值：

UPDATE MovieSurvey 
SET field7 = NULLIF(field7, 'Did not see'), 
    field8 = NULLIF(field8, 'Did not see'), 
    field9 = NULLIF(field9, 'Did not see'), 
    field10 = NULLIF(field10, 'Did not see'), 
    field11 = NULLIF(field11, 'Did not see'), 
    field12 = NULLIF(field12, 'Did not see')
WHERE 'Did not see' IN (field7, field8, field9, field10, field11, field12);

赞(0）回复(0）举报 2023-02-09

我来回答

sqlite 如何压缩这些set和where语句

2条答案

相关问题

热门标签

最新问答