MYSQL and Node.js multiple record insert with where clause

Question

I am trying to insert multiple records into MYSQL from Node.js with a WHERE clause but I keep getting a syntax error.

The statement works fine until I try to add a conditional statement to it. Then I get this error: ER_PARSE_ERROR: You have an error in your SQL syntax near VALUES ? WHERE ...

var Data = data; // this is a nested array already as received from client side like [[..],[..],[..]]
var ID = 123; 

 var sql = "INSERT INTO table1 (Col1,Col2,Col3,Col4,Col5) VALUES ? WHERE"+ID+" NOT IN (SELECT somecol FROM table2 WHERE somecol= "+ID+")"

connection.query(sql, [Data], function (error, result) {
    if (error) {
        throw error;
        res.json({ Message: "Oops something went wrong :("});
    }

    res.json({ Message: "Your data was added!"});
});

The connection is set up to allow multiple statements already:

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '1234',
    database: 'thedb',
    port: 12345,
    charset: "utf8mb4",
    multipleStatements: true
});

The query works in this form without the WHERE clause:

var Data = data; // this is a nested array already as received from client side like [[..],[..],[..]]
var ID = 123; 

var sql = "INSERT INTO table1 (Col1,Col2,Col3,Col4,Col5) VALUES ?"

connection.query(sql, [Data], function (error, result) {
    if (error) {
        throw error;
        res.json({ Message: "Oops something went wrong :("});
    }

    res.json({ Message: "Your data was added!"});
});

How do I get the query work with the WHERE clause?

score 0 · Accepted Answer · answered Nov 25 '18 at 14:31

Insert command will not work with Where clause because you are inserting a new row. In naive terms, a Where clause needs some rows to filter out based on the conditions. Based on your use case you can have two possible solutions:

Use Update statements which could be like Update table set col1=val1 where (condition clause)
If you really want to use Where clause then you can use the Insert command in the following form

Insert into table(col1,col2) Select (val1, val2) from table2 where (condition clause);

Ah yes thank you very much somehow I did not see that ! – Arlo Eardley Nov 25 '18 at 14:58 — Arlo Eardley, Nov 25 '18 at 14:58

MYSQL and Node.js multiple record insert with where clause

1 Answers1