Update with calculation & if statement

[shakeit13aby]

I facing a problem where i cant update my mysql data with calculation and if statement
i want to update my column answer column and make calculation between column one and two depending on column sign.whether to sum minus multiply or divide:S

UPDATE calculation SET answer
IF (sign = +) THEN SET answer=one+two
ELSE IF (sign = -) THEN SET answer=one-two
ELSE IF (sign = *) THEN SET answer=one*two
ELSE
SET answer=one/two
END IF;

Thanks in advance!

regards
WS

[smantscheff]

drop table if exists calculation;
create table calculation (one float, two float, sign enum('+','-','*','/'),answer float);
insert into calculation (one,two,sign) values (1,2,'+'),(1,2,'-'),(1,2,'*');

UPDATE calculation SET answer = 
CASE sign 
WHEN '+' THEN  one+two
WHEN '-' THEN  one-two
WHEN '*' THEN  one*two
ELSE answer=one/two
END
;

+-----+-----+------+--------+
| one | two | sign | answer |
+-----+-----+------+--------+
|   1 |   2 | +    |      3 |
|   1 |   2 | -    |     -1 |
|   1 |   2 | *    |      2 |
+-----+-----+------+--------+

Beware of "0" values in column two.
And what would be the use of calculated values in a table?

[smantscheff]

Please mark this thread as solved.