Can not execute query with IF ... THEN statement

Question

I can not execute query like: and I dont know why:

IF(NOT EXISTS(SELECT accountId FROM test_account WHERE content = '123')) THEN
    INSERT INTO test_account (content) VALUES ('123');
END IF;
SELECT accountId FROM test_account WHERE content = '123' LIMIT 1;

Error received:

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(NOT EXISTS(SELECT accountId FROM test_account WHERE content = '123')) THEN INSE' at line 1

Edit:

I tried to write procedure first:

CREATE FUNCTION getTestAccount(var_c VARCHAR(20)) RETURNS INT
BEGIN
IF NOT EXISTS(SELECT accountId FROM test_account WHERE content = var_c)
THEN
    INSERT INTO test_account (content) VALUES (var_c);
END IF;
RETURN (SELECT accountId FROM test_account WHERE content = var_c LIMIT 1);
END;

I tried with THEN and without...

Can not even query something simple:

CREATE FUNCTION getVar(var INT) RETURNS INT
BEGIN
RETURN var;
END;

Possible duplicate of [Using an if statement outside a stored procedure](https://stackoverflow.com/questions/26358397/using-an-if-statement-outside-a-stored-procedure) — Alex Shesterov, Sep 06 '18 at 11:21
Thin thread seems to solve a problem that looks like yours: https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table/42789261 — Bianca, Sep 06 '18 at 11:42
Your function code looks fine as is. Did you remember to use `DELIMITER //` (or `$$`, or something other than `;`) before the `CREATE FUNCTION` code? Otherwise MySQL gets confused by the `;`s in the function definition. — Nick, Sep 06 '18 at 12:23
@Nick i'm not sure I know how to use it. will be nice if you sugest anser showing usage. — atd_motum, Sep 06 '18 at 12:31
@atd_motum I have posted an answer as requested. Can you give it a try and give some feedback thanks. — Nick, Sep 06 '18 at 12:34

score 0 · Answer 1 · answered Sep 06 '18 at 11:25

0

Is not clear the context you are using the code .. but you could also try an insert select this way

    INSERT INTO test_account (content) 
    SELECT '123' 
    FROM test_account 
    WHERE content = '123' 
    AND   NOT EXISTS(SELECT accountId 
              FROM test_account WHERE content = '123') 
    LIMIT 1;

answered Sep 06 '18 at 11:25

scaisEdge

124,973
10
73
87

I wish to create account if content not exists and return accountId – atd_motum Sep 06 '18 at 11:39

Nick · Accepted Answer · 2018-09-06T12:37:46.510

0

Your function code looks fine. I wonder if you forgot to set the delimiter before entering the function to prevent MySQL being confused by the ;s in the function i.e.

DELIMITER //
CREATE FUNCTION getTestAccount(var_c VARCHAR(20)) RETURNS INT
BEGIN
    IF NOT EXISTS(SELECT accountId FROM test_account WHERE content = var_c) THEN
        INSERT INTO test_account (content) VALUES (var_c);
    END IF;
    RETURN (SELECT accountId FROM test_account WHERE content = var_c LIMIT 1);
END //
DELIMITER ;

edited Sep 06 '18 at 12:37

answered Sep 06 '18 at 12:34

Nick

118,076
20
42
73

`#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 8` got this error – atd_motum Sep 06 '18 at 12:36

Can not execute query with IF ... THEN statement

2 Answers2