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

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

CodePudding user response：

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.

CodePudding user response：

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' )