Search code examples
mysqlsqlalter-table

Add a column which is the divisible by 10 of another column

A table called test has columns :

person_id (Unique) & special_num

Table:

Table view

Outcome :

outcome

Would like to create a new column called div to determine which person got the special_num that is divisible by 10 and which person did not instead of true or false would like to have a yes or no.

I am new to MySQL and have never tried it but gave it a shot please tell me how to get this:

SELECT * FROM test WHERE special_num % 10 = 0 AS div from test;

I am unable to figure out how to input values and if it is the right way of doing it

Solution

  • Here is my SQLFiddle to mimic this problem.

    First step is to create a new column by ALTERing the table.

    ALTER TABLE PERSONS 
     ADD is_special VARCHAR(3);

    Now run an UPDATE query that checks if the special_num % 10 is zero or not, and if it is, then set is_special to yes.

    UPDATE PERSONS SET is_special = CASE WHEN special_num % 10 = 0 THEN 'yes' ELSE 'no' END;

    Having said that, it is a bad idea to store values that are derived or calculated from other fields. You may want to use this in a view, but not in a table.