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
Jump to Postdrop 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 …
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?
Please mark this thread as solved.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.