IF STATEMENT IN MYSQL INSERT is not working

Question

Please, I do not understand why it is not working:

    SET @key = (SELECT customer.key_stamp FROM customer WHERE customer.key_stamp = "0000");
    CASE WHEN (@key > 0) THEN 
        INSERT INTO transaction
                                (transaction.to, transaction.key, transaction.type, transaction.cost_bitcoin, transaction.quantity)
                                VALUES ("0000", "f5rwsd", 2, 0.0075, 
                                        (500000 + 
                                            (
                                                (SELECT bonus.amount
                                                    FROM bonus
                                                    WHERE 500000 BETWEEN bonus.min_quantity AND bonus.max_quantity
                                                ) / 500000
                                            ) * 100))
    END;

I tried the CASE STATEMENT, but it still does not work, and i can't understand the issue. Please help.

What is the definition of `it does not work`? What is the expected output or behavior? What actually you are getting instead? — Chetan, Jul 28 '17 at 00:33
Your problem is not the case statement but how you are attempting to assign the key stamp value to the `@key` variable - see here: https://stackoverflow.com/questions/10741612/set-the-result-of-a-query-to-a-variable-in-mysql — ever.wakeful, Jul 28 '17 at 00:54
although now that I have said that case statements need to be closed with `END CASE` — ever.wakeful, Jul 28 '17 at 00:58
i need check, if customer key is inside my db, make insert, otherwise do nothing. — Denis Rohlinsky, Jul 28 '17 at 02:20

spencer7593 · Answer 1 · 2017-07-28T01:38:57.317

1

CASE is not a valid MySQL statement. (It's not a valid statement outside the context of a MySQL stored program.)

"Why it is not work"... is because it's not valid SQL.

A CASE expression can be used in a SQL statement where an expression is allowed. (In SQL, an expression returns a value of a particular datatype.)

As an example of using a CASE expression in a SQL statement, something like this:

SELECT CASE WHEN @key > 0 THEN 'somevalue' ELSE 'othervalue' END AS foo

edited Jul 28 '17 at 01:38

answered Jul 28 '17 at 00:53

spencer7593

106,611
15
112
140

mysql actually has both a case statement (https://dev.mysql.com/doc/refman/5.7/en/case.html) and a case expression (https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case) – ever.wakeful Jul 28 '17 at 01:05
i need statement for insert, i want when its back true, make insert, when falce, do nothing, and i not whery understand how it make, the SELECT before is not very helpful, becuse i not select anything. – Denis Rohlinsky Jul 28 '17 at 02:17

score 1 · Accepted Answer · answered Feb 18 '18 at 18:07

1

in fact now i use just an sql functions, and it work now, all work, switch case mysql, if else statement mysql, and loops too, like while do, or LOOP ITERATE, all work, just need use it in sql functions, or sql procedure.

answered Feb 18 '18 at 18:07

Denis Rohlinsky

190
1
2
12

IF STATEMENT IN MYSQL INSERT is not working

2 Answers2