Update and Delete in MySQL Table

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

<<-Select in MySQL, Output ..

Daily Test with Code Example

HTML

CSS

JavaScript

PHP-MySQL

Which tag is a block element?

<div>Web Programming and Development</div>

Which CSS code displays the text underlined?

font-style: italic; text-decoration: underline; font-weight: 500;

h2 {
  text-decoration: underline;
}

Click on the JavaScript function that can access other function after a specified time.

insertBefore() setTimeout() querySelector()

function someFunction() { alert("CoursesWeb.net"); }
setTimeout("someFunction()", 2000);

Click on the instruction that returns the number of items of a multidimensional array in PHP.

count($array) count($array, 1) strlen()

$food =["fruits" =>["banana", "apple"), "veggie" =>["collard", "pea"));
$nr_food = count($food, 1);
echo $nr_food;       // 6