Languages

Node.js - MySQL - update error (SQL syntax error)

3 points
Asked by:
Waleed-Gates
352

I was trying to update one record in my database (MySQL), but I got this response:

  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 'role = 'admin' WHERE id = '2'' at line 1",
  sqlState: '42000',
  index: 0,
  sql: "UPDATE users SET name = 'Christopher' role = 'admin' WHERE id = '2'"

My code: 

con.connect(err => {
    if (err) throw err;
    let sql = 'UPDATE users SET name = ? role = ? WHERE id = ?';
    let values = ["Christopher", "admin", "2"]

    con.query(sql, values, (err, result) => {
        if (err) throw err;
        console.log(result);
    });
});

Does anyone know how to fix this?

1 answer
4 points
Answered by:
Waleed-Gates
352

In your SQL query coma is missing (,).

Should be UPDATE users SET name = ?, role = ? WHERE id = ?

My advice is to wrap table and column names into `` too - it prevents against the situation when we use a keyword as a table or column name.

I had in the past situation with MySQL where virtual world was the name of the column. After MySQL upgrade (I have changed to MariaDB), the application crashed because they introduced virtual keyword - `virtual` was the solution for it.

Fixed code:

con.connect(err => {
    if (err) throw err;
    let sql = 'UPDATE `users` SET `name` = ?, `role` = ? WHERE `id` = ?';
    let values = ['Christopher', 'admin', '2']

    con.query(sql, values, (err, result) => {
        if (err) throw err;
        console.log(result);
    });
});

BTW: it is good to use '' instead of "" for strings in JS code.

0 comments Add comment
Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join