How do i condense these set and where statements-CodePudding

I would like to update all values of 'did not see' in a single table to NULL.

I cannot figure out how to write in one set statement

This is the code which I would like to write more elegantly

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'

Thanks for the help!

CodePudding user response：

You have to reference each column you want to update, but you could use a case expression in a single update.

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

CodePudding user response：

Use the function NULLIF() which will return either NULL if the column's value is 'Did not see' or else the actual value of the column:

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);