Node.js MySQL - UPDATE Table Query

Node.js MySQL UPDATE Table Query

The Node.js MySQL UPDATE query is used to modify existing rows in a MySQL table from a Node.js application. You can update one row, multiple rows that match a condition, or every row in a table when the query does not include a WHERE clause.

In this tutorial, we will learn how to update records of a table based on WHERE clause, and also learn how to update all records of a table.

The examples use the mysql package, a studentsDB database, and a students table. The same UPDATE pattern applies to other tables: connect to MySQL, prepare the UPDATE statement, execute it with con.query(), and check the result object to confirm how many rows were matched and changed.

MySQL Update record(s) of a table
MySQL Update all records of a table
Node.js MySQL UPDATE with values passed safely
How to read affectedRows and changedRows
Node.js MySQL UPDATE query checklist
Node.js MySQL UPDATE query FAQs

Node.js MySQL UPDATE query syntax

The basic MySQL UPDATE statement sets one or more column values in existing rows. In Node.js, the SQL statement is passed as the first argument to con.query().

</>

Copy

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

The WHERE clause is important when only selected rows should be updated. Without it, MySQL applies the new value to every row in the table.

Example 1 – Update Row(s) of MySQL Table via Node.js

Consider the case that due to manual error, records in the students table are inserted with marks as 74 instead of 84. Now we shall execute a MySQL Update Query that updates marks column with value 84 where there is a value of 74.

This query updates only the rows where marks=74. If no row has marks equal to 74, the query still runs successfully, but no row is changed.

UpdateRecordsFiltered.js

</>

Copy

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("UPDATE students SET marks=84 WHERE marks=74", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Open a terminal or command prompt and run this script using node command as shown in the following.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node UpdateRecordsFiltered.js 
OkPacket {
  fieldCount: 0,
  affectedRows: 3,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '(Rows matched: 3  Changed: 3  Warnings: 0',
  protocol41: true,
  changedRows: 3 }

In this output, affectedRows: 3 means MySQL found three rows that matched the condition. changedRows: 3 means all three matching rows received a different value and were actually changed.

Example 2 – Update All Records of MySQL Table via Node.js

Consider a scenario where the exam conducted to the students is cancelled due to some reason, you want to reconduct the exam. So, you would like to update the marks for all the students to 0. Following Node.js MySQL UPDATE Query Example shows how to update all the records of a table.

UpdateAllRecords.js

</>

Copy

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("UPDATE students SET marks=84", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Observe that there is no WHERE clause in the Query because of which all the records are selected for update process.

Use this form only when the application really needs to change every row. For example, an admin-only maintenance script may reset a column for the complete table. In most application screens, an UPDATE query should include a clear condition such as a primary key, email, roll number, or status value.

Node.js MySQL UPDATE with values passed safely

In real applications, UPDATE values usually come from a form, API request, or another part of the program. Instead of joining those values directly into the SQL string, pass them as query parameters. This keeps the query easier to read and helps avoid errors caused by quotes or special characters in user input.

The following example updates the marks of a student by using placeholder values in the UPDATE statement.

</>

Copy

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "arjun",
  password: "password",
  database: "studentsDB"
});

var newMarks = 84;
var studentId = 5;

con.connect(function(err) {
  if (err) throw err;

  var sql = "UPDATE students SET marks = ? WHERE id = ?";

  con.query(sql, [newMarks, studentId], function(err, result) {
    if (err) throw err;

    console.log("Rows matched: " + result.affectedRows);
    console.log("Rows changed: " + result.changedRows);
  });
});

Here, the first ? receives the value of newMarks, and the second ? receives the value of studentId. The values are supplied in the same order as the placeholders appear in the SQL query.

Update multiple MySQL columns from Node.js

A single UPDATE query can change more than one column. Separate each column assignment with a comma in the SET clause.

</>

Copy

var sql = "UPDATE students SET marks = ?, result = ? WHERE id = ?";
var values = [84, "PASS", 5];

con.query(sql, values, function(err, result) {
  if (err) throw err;

  console.log("Rows changed: " + result.changedRows);
});

This query updates both marks and result for the student whose id is 5. The WHERE clause keeps the change limited to the intended record.

How to read Node.js MySQL UPDATE result values

After an UPDATE query runs, the callback receives a result object. The most useful values are usually affectedRows and changedRows.

Result property	Meaning after a MySQL UPDATE query
`affectedRows`	Number of rows matched by the UPDATE condition.
`changedRows`	Number of rows where MySQL actually changed the stored value.
`warningCount`	Number of warnings generated by the query.
`message`	MySQL status message, often including matched, changed, and warning counts.

affectedRows and changedRows can be different. For example, if a row already has marks = 84 and the query sets marks to 84 again, MySQL may match the row but not count it as changed.

Common mistakes in Node.js MySQL UPDATE queries

Missing WHERE clause: This updates every record in the table. Use it only when that is the intended result.
Wrong placeholder order: Values passed in the array must match the order of ? placeholders in the SQL query.
Not checking changedRows: A successful query does not always mean a row value changed.
Using direct string concatenation: Avoid building SQL strings by directly joining user-entered values.
Leaving the connection open in short scripts: For one-time scripts, close the connection after the query using con.end().

Node.js MySQL UPDATE query checklist

Confirm the table name and column names used in the UPDATE query.
Use a WHERE clause unless the script must update the complete table.
Use placeholders for values passed from forms, APIs, or variables.
Check affectedRows to know how many rows matched the condition.
Check changedRows to know how many rows were actually modified.
Test the UPDATE query on sample data before running it on important records.

Node.js MySQL UPDATE query FAQs

How do I update a row in MySQL using Node.js?

Create a MySQL connection, write an UPDATE statement, and execute it with con.query(). Use a WHERE clause such as WHERE id = ? to update only the intended row.

What happens if I run a Node.js MySQL UPDATE query without WHERE?

MySQL updates every row in the table. This is useful only for table-wide changes. For normal record editing, include a WHERE clause to limit the update.

How can I update multiple columns in one Node.js MySQL query?

Add multiple assignments inside the SET clause, separated by commas. For example, use SET marks = ?, result = ? and pass the values in the same order.

Why is affectedRows different from changedRows in a MySQL UPDATE result?

affectedRows counts the rows that matched the UPDATE condition. changedRows counts the rows whose stored value was actually changed. If a row already had the same value, it may be matched but not changed.

Can I pass variables to a Node.js MySQL UPDATE query?

Yes. Use placeholders in the SQL statement and pass the values as an array to con.query(). This is cleaner and safer than inserting variable values directly into the SQL string.

Conclusion

In this Node.js Tutorial – Node.js MySQL – UPDATE Table Query, we have learnt to UPDATE records based on a condition or UPDATE all the records of a table.

The safest pattern is to use an UPDATE statement with a specific WHERE condition, pass dynamic values through placeholders, and read the result object to confirm whether the intended rows were matched and changed.

TutorialKart.com

Node.js MySQL – UPDATE Table Query – Examples