How to alter the selected column value based on the value of another column in SQL?

  • How-Tos FAQs
  • December 17, 2018
How to alter the selected column value based on the value of another column in SQL?Supercharge Your Snowflake SQL
with Datameer's
Data Transformation

For MySQL set column value based on another column, we have two options:

  • IF function
  • CASE statement
CREATE TABLE score (
      player VARCHAR(100),
       win_loss VARCHAR(10),
       point INT   
);

INSERT INTO  score
SELECT 'Rose', 'loss', 4 UNION ALL
SELECT 'Jack', 'win', 40 ;

MYSQL

-- using IF function
SELECT player,
       IF(win_loss = 'loss', point* -1, point ) score
FROM score;

-- using CASE statement
SELECT player,
       CASE WHEN win_loss = 'loss'
              THEN point* -1
        ELSE point
       END score
FROM score

-- Output of both of the query is same as below
# player    score
---------------------
Rose         -4
Jack         40

SQL SERVER

In SQL Server, CASE statements are the same as in MySQL. But the IF function in SQL Server is named IIF .

-- using IIF function
SELECT player,
       IIF(win_loss = 'loss', point* -1, point ) score
FROM score;

-- using CASE statement
SELECT player,
       CASE WHEN win_loss = 'loss'
             THEN point* -1
        ELSE point
       END score
FROM score

-- Output of both of the query is same as below
player  score
---------------------
Rose    -4
Jack    40

Up Next:

Read How to add a column with a default value to an existing table in SQL?