Create SQL column relative to another

Question

I have a column with different values :

1 
2 
4 
8

I want to create a new column in the same table based on these values.

For example, if the value is 1 or 4, I want to display the corresponding line in OK and NOT OK for the rest.

Like:

1 OK 
2 NOT OK 
4 OK 
8 NOT OK 

I think we should use an if condition, but how to create at same time with the column values ​​in the table?

Answer 1

Better use Case statement.

SELECT 
Case when id in (1,4) Then 'Ok' Else 'Not Ok' End as Myfield
FROM  MyTable

Fiddle

Answer 2

Since you say "I want to display" I'll give you some If Snippets

This is how it works, Simply for ex:

SELECT IF(field= '1', 'OK', 'NOT OK') as Myfield
FROM  MyTable

or

SELECT IF(field= '1', 'OK', IF(field= '4', 'OK', 'NOT OK')) as Myfield
FROM  MyTable

;So here the result will be `OK` if there is any 1, 4 or else `NOT OK`

Here is MySQL Documentation about If Conditions

Answer 3

Do you want a new column in your table or just in the SELECT output? If you need a new table column, add it and then run an UPDATE query.

UPDATE `mytable`
SET `result` = CASE 
   WHEN id in (1,4) THEN 'ok'
   ELSE 'not ok' END;

SQL Fiddle