EN
Node.js - MySQL - update error (SQL syntax error)
1
answers
3
points
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
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