Node.js MySQL Update

The UPDATE statement in MySQL is used to modify existing records in a table. You can update one or more columns for rows that match a specific condition.

 

Key Features of Node.js MySQL Update

1. Update Specific Rows: You can update specific rows based on a condition using the WHERE clause.
2. Update Multiple Columns: Multiple columns can be updated in a single query.
3. Transactional Updates: Updates can be wrapped in a transaction to maintain data integrity.
4. Conditional Updates: You can use various operators (=, <, >, LIKE, etc.) in the WHERE clause to filter rows for update.

 

Step 1 Prerequisites

Ensure that MySQL is installed and the mysql2 package is added to your Node.js project.

npm install mysql2

Step 2 Basic Query to Update Data

The basic structure for the UPDATE statement is as follows:

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

Example Code for Updating Data

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Update record in the users table where name is 'John Doe'
  const updateQuery = 'UPDATE users SET age = 30 WHERE name = "John Doe"';

  connection.query(updateQuery, (err, results) => {
    if (err) {
      console.error('Error updating record:', err.stack);
      return;
    }
    console.log('Record updated:', results.affectedRows);
  });

  // Close the connection
  connection.end();
});

Output:

Record updated: 1

This result indicates that the age of the user 'John Doe' has been updated to 30.

Step 3 Update Multiple Columns

You can update more than one column in a single query.

Example Code for Updating Multiple Columns

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Update multiple columns where name is 'Jane Doe'
  const updateQuery = 'UPDATE users SET age = 28, email = "[email protected]" WHERE name = "Jane Doe"';

  connection.query(updateQuery, (err, results) => {
    if (err) {
      console.error('Error updating record:', err.stack);
      return;
    }
    console.log('Record updated:', results.affectedRows);
  });

  // Close the connection
  connection.end();
});

Output:

Record updated: 1

This result indicates that both the age and email for 'Jane Doe' have been updated.

Step 4 Update with Conditions

You can use different conditions in the WHERE clause to target specific rows.

Example Code for Conditional Update

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Update records where the age is greater than 30
  const updateQuery = 'UPDATE users SET status = "active" WHERE age > 30';

  connection.query(updateQuery, (err, results) => {
    if (err) {
      console.error('Error updating record:', err.stack);
      return;
    }
    console.log('Records updated:', results.affectedRows);
  });

  // Close the connection
  connection.end();
});

Output:

Records updated: 3

This result shows that 3 records have been updated where the age is greater than 30.

Step 5 Using Transactions for Update

To ensure data integrity, you can use transactions when updating data.

Example Code with Transaction

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Start a transaction
  connection.beginTransaction((err) => {
    if (err) {
      console.error('Error starting transaction:', err.stack);
      return;
    }

    // Update record where name is 'Sam Smith'
    const updateQuery = 'UPDATE users SET age = 35 WHERE name = "Sam Smith"';

    connection.query(updateQuery, (err, results) => {
      if (err) {
        // Rollback the transaction if there's an error
        connection.rollback(() => {
          console.error('Error updating record, transaction rolled back:', err.stack);
        });
        return;
      }

      // Commit the transaction
      connection.commit((err) => {
        if (err) {
          console.error('Error committing transaction:', err.stack);
        } else {
          console.log('Transaction committed, record updated');
        }
      });
    });
  });

  // Close the connection
  connection.end();
});

Output:

Transaction committed, record updated

Step 6 Using LIMIT with Update

You can limit the number of rows updated by using the LIMIT clause.

Example Code for Update with Limit

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Update only 2 records where the status is 'inactive'
  const updateQuery = 'UPDATE users SET status = "active" WHERE status = "inactive" LIMIT 2';

  connection.query(updateQuery, (err, results) => {
    if (err) {
      console.error('Error updating record:', err.stack);
      return;
    }
    console.log('Limited records updated:', results.affectedRows);
  });

  // Close the connection
  connection.end();
});

Output:

Limited records updated: 2

 

Summary

The UPDATE statement in MySQL allows you to modify one or more columns of specific rows based on conditions. You can update multiple columns at once, apply conditions using the WHERE clause, and use transactions for data integrity. Always use a WHERE clause to avoid unintentionally updating all rows. You can also limit updates using LIMIT and manage atomicity with transactions.