The query() method of the "mysql" module can perform SQL queries that update and delete records in mysql table.
To avoid SQL Injection, you should escape any user provided data before using it inside a SQL query. You can do so using the mysql.escape() method.
Alternatively, you can use ? characters as placeholders for values you would like to have escaped, and add the values into an array as second argument of query() method (in the same order as placeholders in sql query).

Node.js MySQL Update

You can update existing records in a table by using the "UPDATE" statement.
To get the number of affected /updated rows, use the affectedRows property of the result object.

- In this example we overwrite the "address" column of the row with id 5, using mysql.escape() method to escape data in SQL query:
const mysql = require('mysql');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

//update with escape()
var adr ='Happy 12';
var id =5;
let sql ='UPDATE friends SET address ='+ mysql.escape(adr) +' WHERE id ='+ mysql.escape(id);

pool.getConnection((err, con)=>{
  if(err) throw err;

  con.query(sql, (err, res)=>{
    if(err) throw err;
    con.release(); //release the connection
    console.log('Record(s) updated: '+ res.affectedRows);
  });
});
- Here is the same update sql query, with placeholders:
const mysql = require('mysql');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

//update using placeholders
let sql ='UPDATE friends SET address =? WHERE id =?';
var placeh =['Happy 12', 5]; //values for placeholders

pool.getConnection((err, con)=>{
  if(err) throw err;

  con.query(sql, placeh, (err, res)=>{
    if(err) throw err;
    con.release(); //release the connection
    console.log('Record(s) updated: '+ res.affectedRows);
  });
});
- Another method with placeholders, adding the column names and values in an objects:
const mysql = require('mysql');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

//update using placeholders
let sql ='UPDATE friends SET ? WHERE ?';
var adr ={address:'Happy 12'};
var id ={id:5};

pool.getConnection((err, con)=>{
  if(err) throw err;

  con.query(sql, [adr, id], (err, res)=>{
    if(err) throw err;
    con.release(); //release the connection
    console.log('Record(s) updated: '+ res.affectedRows);
  });
});
Results:
Record(s) updated: 1

Node.js MySQL Delete

You can delete records from an existing table by using the "DELETE FROM" statement.
To get the number of affected /deleted rows, use the affectedRows property of the result object.
const mysql = require('mysql');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

//deleted with placeholders
let sql ='DELETE FROM friends WHERE id =? OR name =?';
var id =2;
var name='Olpram';

pool.getConnection((err, con)=>{
  if(err) throw err;

  con.query(sql, [id, name], (err, res)=>{
    if(err) throw err;
    con.release(); //release the connection
    console.log('Rows deleted: '+ res.affectedRows);
  });
});
Results:
Rows deleted: 2

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which HTML5 tag is indicated to be used as container for menu with navigation links in Web site?
<section> <nav> <article>
<nav><ul>
 <li><a href="http://coursesweb.net/css/" title="CSS Course">CSS Course</a></li>
 <li><a href="http://www.marplo.net/jocuri/" title="Flash Games">Flash Games</a></li>
</ul></nav>
Which CSS property shifts an item horizontally to the left or right of where it was?
text-align clear float
.some_class {
  width: 30%;
  float: left;
}
Click on the Math object method which returns x, rounded downwards to the nearest integer.
Math.ceil(x) Math.abs(x) Math.floor(x)
var num = 12.34567;
num = Math.floor(num);
alert(num);       // 12
Indicate the PHP function which returns the number of characters in string.
mb_strlen() count() stristr()
$str = "string with utf-8 chars åèö";
$nrchr = mb_strlen($str);
echo $nrchr;        // 30
Update and Delete in MySQL Table

Last accessed pages

  1. PHP-MySQL free course, online tutorials PHP MySQL code (50606)
  2. SHA1 Encrypt data in JavaScript (14229)
  3. Insert, Select and Update NULL value in MySQL (46603)
  4. PHP getElementById and getElementsByTagName (32879)
  5. Get Attribute (ID, Class, Name, Title, Src) with jQuery (64120)

Popular pages this month

  1. Making DIV Contents Scroll Horizontally, with multiple Div`s inside (1440)
  2. Contact page - CoursesWeb (1403)
  3. Tabs effect with CSS (1398)
  4. PHP getElementById and getElementsByTagName (661)
  5. Get Attribute (ID, Class, Name, Title, Src) with jQuery (538)