0

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

2
Contributors
2
Replies
5
Views
6 Years
Discussion Span
Last Post by smantscheff
1
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?

Edited by smantscheff: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.