How to alter the selected column value based on the value of another column in SQL?
- How-Tos FAQs
- December 17, 2018
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 40SQL 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 40Up Next:
Read How to add a column with a default value to an existing table in SQL?
