CODEWITHSUNDEEP
mysql
soft genic
soft genic

Reputation: 2056

Record Insertion - If zero insert zero else apply formula

Following is my query in which i am trying to apply formula on users.i_dob it if the insertion value is not zero else the query should insert zero. Kindly let me know how can i modify the following actual query in order to achieve my functionality thanks,

Actual QUERY:

 INSERT IGNORE into age ( i_age)
                SELECT DISTINCT FLOOR((TO_DAYS(NOW())- TO_DAYS(FROM_UNIXTIME(users.i_dob))) / 365.25)
            FROM users

What I am trying to do with the query:

INSERT IGNORE into z_census ( i_age)
            SELECT IF (i_age == 0) i_age else FLOOR((TO_DAYS(NOW())- TO_DAYS(FROM_UNIXTIME(users.i_dob))) / 365.25)
            FROM users

Upvotes: 0

Views: 58

Answers (2)

cegfault
cegfault

Reputation: 6632

See the MySQL IF syntax. It should look something like this:

INSERT IGNORE into z_census (i_age)
SELECT IF(i_age=0, i_age,
    FLOOR((TO_DAYS(NOW())- TO_DAYS(FROM_UNIXTIME(users.i_dob))) / 365.25))
FROM users

Upvotes: 1

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

INSERT IGNORE into z_census ( i_age)
            SELECT IF((i_age = 0), i_age, FLOOR((TO_DAYS(NOW())- TO_DAYS(FROM_UNIXTIME(users.i_dob))) / 365.25))
            FROM users

Upvotes: 1

Related Questions