updating column of a table if the validating column value is null in postgres-CodePudding

I am new to postgres and trying to update the columns where the status is either null or new. The below query is validating for NEW but not for empty string/null value. Is there any way to achieve this?

update test_table c set FLAG = -1, err_det='Incorrect entry' where FName is not null and  Status in ('NEW','')

This query is being used inside a function.

CodePudding user response：

I think this will help you if your default value is NULL

update test_table c set FLAG = -1, err_det='Incorrect entry' where FName is not null and  (Status IS NULL OR Status='NEW');

Considering change Status in ('NEW','') to (Status IS NULL OR Status='NEW')

CodePudding user response：

Reading your question I was unsure if you wanted to update just NULL and the 'NEW' value or 'empty string/null value' as you mentioned.

Below will work for the values NULL/'NEW'/ and 'empty string'.

UPDATE test_table c 
SET FLAG = -1, err_det='Incorrect entry' 
WHERE FName is NOT NULL AND  
(Status IS NULL OR Status='NEW' OR Status='')

Below just NULL and 'NEW'.

UPDATE test_table c 
SET FLAG = -1, err_det='Incorrect entry' 
WHERE FName IS NOT NULL AND  
(Status IS NULL OR Status='NEW')