mySQL syntax error while using SELECT IF(EXISTS()) in node.js client

Question

I have a SQL query in node.js for my proposing system The feature should insert data when there isn't included same data in current table.

    var sql= `
    SELECT IF(
        EXISTS(SELECT student_id FROM propose_${propose} WHERE student_id = '${user}'),
        (INSERT INTO propose_${propose} (student_id) VALUES ('${user}')),
        NULL
    )    
    `
    client.query(sql,(err,data)=>{
        if(err)throw err;
        console.log(data)
    })

as the code,query should be sent as:

SELECT IF(
EXISTS(SELECT student_id FROM propose_a WHERE student_id = 'account'),
(INSERT INTO propose_a (student_id) VALUES ('account')),
NULL
)

but it returns SQL syntex error

code: 'ER_PARSE_ERROR',


errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO propose_a (student_id) VALUES ('account')),\n" +
    '            NULL\n' +
    "        )' at line 3",
  sqlState: '42000',

The problem might arose around "INSERT INTO" in the line 3 of sql query

You describe "insert when the data ***isn't*** present" but then your code logic expresses "insert when the data ***is*** present". Which do you actually need? — MatBailie, Sep 04 '22 at 05:16

score 0 · Answer 1 · answered Sep 04 '22 at 04:27

Your usage of the if function of MySQL is wrong.

IF(expression, expr_true, expr_false);

Return type of expr_true and expr_false should either be an integer, float or string. You're returning a mysql insert statement in expr_true which is syntactically wrong.

From your query, it looks like you're trying to insert into the table if the record doesn't exist. See examples of how to do that here.

score 0 · Accepted Answer · answered Sep 04 '22 at 05:03

0

IF() function needs in scalar expression whereas you try to use INSERT query.

Use INSERT .. SELECT with according WHERE:

INSERT INTO propose_a (student_id) 
SELECT 'account'
WHERE EXISTS ( SELECT student_id 
               FROM propose_a 
               WHERE student_id = 'account' )

answered Sep 04 '22 at 05:03

Akina

39,301
5
14
25

Or just `INSERT INTO x(foo) SELECT DISTINCT y.foo FROM y WHERE y.foo = 'bar'`? – MatBailie Sep 04 '22 at 05:08
@MatBailie If there is more than one row in `y` which matches `WHERE y.foo = 'bar'` then your code will insert according amount of copies. – Akina Sep 04 '22 at 05:21
Umm, that's why there's a DISTINCT... – MatBailie Sep 04 '22 at 13:26
@MatBailie ... and performance degrade. – Akina Sep 04 '22 at 15:55

mySQL syntax error while using SELECT IF(EXISTS()) in node.js client

2 Answers2