How to update multiple columns having the same value or different values in SQL?-CodePudding

I want to update multiple columns in a table having the same value. For example if we consider this table.

col1     col2
--------------
2        -99
-99        5
3          6
4        -99

I want to update -99 values in the table to NULL and the expected result looks like this.

col1     col2
--------------
2        NULL
NULL        5
3           6
4        NULL

I am using this way.

update table_name set col1 = null where col1 = -99;
update table_name set col2 = null where col2 = -99;

Or what if I want to update columns on unique conditions. For example -99 with null in column1 and 5 with null in column2.

Is there a way to achieve this in a single statement ? Thanks in advance.

CodePudding user response：

You can, by using a case expression, but whats the advantage?

update table_name set
  col1 = case when col1 = -99 then null /* or any new value for col1 */ else col1 end
  , col2 = case when col2 = -99 then null /* or any new value for col2 */ else col2 end
where col1 = -99 or col2 = -99;

Note, as pointed out by Larnu, when you are setting the column to null you can simplify the update to:

update table_name set
  col1 = nullif(col1,-99)
  , col2 = nullif(col2,-99)
where col1 = -99 or col2 = -99;

And you can change the values you are using (-99) on a per column basis to whatever you want e.g. col2 = 5

update table_name set
  col1 = nullif(col1,-99)
  , col2 = nullif(col2,5)
where col1 = -99 or col2 = 5;

CodePudding user response：

You can use NULLIF.

UPDATE null_table
SET co1 = NULLIF(col1,-99)
,col2 = NULLIF(col2,-99)